Date Formatting issue for a field with different formats in source

[pranaview]

Hi All,

I have a field in the source which has values in different date formats for ex: DD/MM/YYYY, Numeric, text,DD-MM-YYYY,MM/DD/YYYY etc. In the source file some are formatted as General and others as Date.

I am converting all these formats into one i.e. 'MM/DD/YYYY' during QVD generation using Alt() function but when the data is in DD/MM/YYYY or D/MM/YYYY format then it is not being converted into MM/DD/YYYY for ex: 05/11/2019 is 'DD/MM/YYYYY' format in source but when it's get loaded into qlik, it stays 05/11/2019 but format changes to MM/DD/YYYY which is a big issue, since from 5th Nov it got changed to 11th May. Not sure how to distinguish between DD/MM/YYYY and MM/DD/YYYY formats.

Update: So i checked the source data and these DD/MM/YYYY or D/MM/YYYY values are already formatted as 'M/DD/YYYY' which means the date 05/11/2019 which is supposed to be 5th Nov is already saved as 'M/DD/YYYY' and hence getting loaded as 11th May in qlik. Is there anyway that i can manipulate this in qlik or this has to be rectified in source file only?

Please let me know how can i handle this as this is creating lot of issues.

Thanks,

Pranav

[Arthur_Fong]

Is it coming from different data source?

If yes, you can try date() function to format  the dates accordingly at script level:

Date([Date column],'DD/MM/YYYY') AS [Date column]

If it is coming from the same source, and there is an identifier to justify which row is [MM/DD/YYYY] or [DD/MM/YYYY],

Try: 

if([identifier]='A', Date([Date column],'DD/MM/YYYY'), Date([Date column],'MM/DD/YYYY') as [Date column]

[marcus_sommer]

If there are no further information you could not reliable distinguish between DD/MM/YYYY and MM/DD/YYYY.

Your used alt() approach is in general quite suitable for your task. You may extend your logic to additionally queries if the fielvalue is a number or a string and also to enforce the value as string - means something like:

date(date#(text(YourField), Format), Format)

Maybe the above suggestion is enough with the variety of your values. If not you could try to consider further information, for example the filename(), the sheetname, other fieldvalues in your data (maybe there are other (linked-) period-fields available) or if the source-data are sorted you could check the values from n previous records or something similar.

I think any of the above mentioned measures should be applicable and resolve your issue or at least reduce the number of possible wrong values - and you may flag them to be able to exclude them from further calculations.

- Marcus

[Brett_Bleess]

Pranav, did Arthur or Marcus' posts help you?  If so, be sure to use the Accept as Solution button to mark the post(s) that helped.  If you did something different, consider posting what you did and then mark that afterward.  If you are still working on things, leave an update for us.

Regards,
Brett

[pranaview · Accepted solution]

Hi Brett,

Since the issue was from the source end of the downloaded reports, it had to fixed in the source file so I didn't have to do anything in Qlik which is why i couldn't tick any of the solution provided. 

Regards,
Pranav