...
First, you must split the column based on the cell value's delimiter. Note that the following transformation uses the
d-s-itemlang item pattern {delim}to locate the delimiter in the cell value. This delimiter is either a dash or a slash.D trans p03Value `{delim}` Type step p01Name Column p01Value contractDate p02Name Option p02Value by Delimiter p03Name Delimiter SearchTerm Split by delimiter Create the following three conditional transformations for extracting the day, month, or year values based on the value in the Region column. Here is the transformation to acquire the year values:
D trans p03Value 'EU' p06Name Value 2 p01Name Condition type p06Value contractDate3 p03Name Case 1 p07Value 'CHN' p04Value contractDate3 SearchTerm conditions p07Name Case 3 Type step p05Name Case 2 p01Value Case on single column p02Name Column to evaluate p02Value Region p05Value 'USA' p04Name Value 1 p08Value contractDate1 p08Name Value 1 For month:
D trans p03Value 'EU' p06Name Value 2 p01Name Condition type p06Value contractDate1 p03Name Case 1 p07Value 'CHN' p04Value contractDate2 SearchTerm conditions p07Name Case 3 Type step p05Name Case 2 p01Value Case on single column p02Name Column to evaluate p02Value Region p05Value 'USA' p04Name Value 1 p08Value contractDate2 p08Name Value 1 For day:
D trans p03Value 'EU' p06Name Value 2 p01Name Condition type p06Value contractDate2 p03Name Case 1 p07Value 'CHN' p04Value contractDate1 SearchTerm conditions p07Name Case 3 Type step p05Name Case 2 p01Value Case on single column p02Name Column to evaluate p02Value Region p05Value 'USA' p04Name Value 1 p08Value contractDate3 p08Name Value 1 You can now bring together these three columns:
D trans p03Value newDate Type step p01Name Columns p01Value day, month, year p02Name Separator p02Value '/' p03Name New column name SearchTerm Merge columns - You now have your new date column. You may need to reformat it into a preferred format.
- Delete the columns that were created during this process.
