General Discussions

Discuss any topics that are not product-specific here.
SOLVED

Put a specific date base on date type

rafatomillero
6 - Meteoroid

I have a column called Invoice Date with mixed date formats, including some NULL, e.g.: 12/05/2022 or 22-05. I want to change the ones with format yy-MM (22-05) and change it to dd/MM/yyyy where dd is 01 (01/05/2022). How do I achieve this?

6 REPLIES 6
DataNath
14 - Magnetar

Here's one way I can think to do that @rafatomillero - let me know if you have any issues when testing it with a wider data set. You'll just need to replace [Mixed date] with your [Invoice Date] column in the expression of course (edit: included below).

 

 

IF REGEX_Match([Mixed date], '\d{2}-\d{2}') THEN DateTimeFormat(DateTimeParse([Mixed date], '%y-%m'),'%d/%m/%Y') ELSE [Mixed date] endif

 

 

DataNath_0-1658849994197.png

 

Your expression:

 

 

IF REGEX_Match([Invoice Date], '\d{2}-\d{2}') THEN DateTimeFormat(DateTimeParse([Invoice Date], '%y-%m'),'%d/%m/%Y') ELSE [Invoice Date] endif

 

rafatomillero
6 - Meteoroid

Hi Nathan,

 

Thank you for your reply.

 

I copied and pasted the code you provided, and Alteryx doesn't give me any error. However, I cannot see any changes in my output file.

 

Please let me know if you know the reason why.

 

Kind regards,

Rafa

DataNath
14 - Magnetar

@rafatomillero are you able to share your workflow, perhaps with mock data, or show a screenshot? If you aren't getting an error then I'm not sure why the changes wouldn't be applied. Is there any chance those records have extra whitespace in them or any other characters etc? Like ' 22-05' or '22-05 ' - something like that would throw off the expression.

rafatomillero
6 - Meteoroid

I just realised that the dates are '22-05 ' with a space at the end. How do I amend this in the code above?

DataNath
14 - Magnetar

Could just add a data cleanse tool ahead of the formula tool to clean up the field. However, to avoid extra tools you just need to add a space to the pattern that the Regex_Match function is looking for so should be this instead:

 

IF REGEX_Match([Invoice Date], '\d{2}-\d{2} ') THEN DateTimeFormat(DateTimeParse([Invoice Date], '%y-%m'),'%d/%m/%Y') ELSE [Invoice Date] endif

 

rafatomillero
6 - Meteoroid

It worked :-) thank you very much! you saved me plenty of time.

Labels