Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Converting multiple date formats

I have a region and date field loaded in as below :

Region,

date#(Date,'MM/DD/YYYY') as SALES _DATE

However where Region = Europe my date formats are in DD/MM/YYYY. For instance in my raw data  I have dates such as 1/4/2013, 1/9/2013 which really should read 4/1/2013, 9/1/2013. How do I load these specific dates ie where region = america differently ?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I think there is the field Date missing in second date#() function, I assume this is only a copy & paste error in the post?

if(Region='Americas', date#(Date,'DD/MM/YYYY'),date#(Date, 'MM/DD/YYYY')) as SALES_DATE,

View solution in original post

8 Replies
maxgro
MVP
MVP

you can change format depending on Region

Region,

if(Region='............',  date#(Date,'MM/DD/YYYY'),

if(Region='............',  date#(Date,'DD/MM/YYYY'),

                                   date#('DD-MM-YYYY')    // default format

)) as SALES _DATE

Not applicable
Author

Thanks maxgro

This is what I have but I realised it's now only showing the first date value for each month which is really odd :

if(Region='Americas', date#(Date,'DD/MM/YYYY'),date#('MM/DD/YYYY')) as SALES_DATE,

swuehl
MVP
MVP

Shouldn't you use

if(Region='Americas', date#(Date,'MM/DD/YYYY'),date#('DD/MM/YYYY')) as SALES_DATE,


or

if(Region='Europe', date#(Date,'DD/MM/YYYY'),date#('MM/DD/YYYY')) as SALES_DATE,


?


A wrong date format might explain that some dates couldn't be interpreted, but it wouldn't explain that you only see first date for each month.

Not applicable
Author

Thanks swuehl but I can't see how that's any different from my original code.

Just to confirm it's only the 'Americas' region which is currently in DD/MM/YYYY. The rest of the regions would be loaded as normal.

swuehl
MVP
MVP

My code differs to your code posted in your second post in the format codes used.

Just to confirm it's only the 'Americas' region which is currently in DD/MM/YYYY. The rest of the regions would be loaded as normal.

I am little confused. In your original post, you said:

...However where Region = Europe my date formats are in DD/MM/YYYY

Not sure if I understand your issue, could you post some sample lines of data?


Not applicable
Author

Sorry let me clarify.

I have a number of regions aside from Americas where the date format is already in MM/DD/YYYY.

Only for Americas are the dates in DD/MM/YYYY and these need to be converted.

Regard to only seeing one date value for each month - I've worked out what's happening. Basically the script is 'only' loading the 'Americas' dates whereas it should be loading all dates and converting the format where necessary.

Hope that clarifies.

swuehl
MVP
MVP

I think there is the field Date missing in second date#() function, I assume this is only a copy & paste error in the post?

if(Region='Americas', date#(Date,'DD/MM/YYYY'),date#(Date, 'MM/DD/YYYY')) as SALES_DATE,

Not applicable
Author

I literally just spotted that ! Good call ... thanks