topic Re: Converting multiple date formats in QlikView

Converting multiple date formats

Thu, 01 Oct 2015 18:55:05 GMT

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 ?

Re: Converting multiple date formats

maxgro — Thu, 01 Oct 2015 19:21:50 GMT

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

Re: Converting multiple date formats

Thu, 01 Oct 2015 21:14:55 GMT

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,

Re: Converting multiple date formats

swuehl — Thu, 01 Oct 2015 21:32:22 GMT

Shouldn't you use

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

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.

Re: Converting multiple date formats

Thu, 01 Oct 2015 22:26:25 GMT

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.

Re: Converting multiple date formats

swuehl — Thu, 01 Oct 2015 22:40:52 GMT

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?

Re: Converting multiple date formats

Thu, 01 Oct 2015 22:45:21 GMT

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.

Re: Converting multiple date formats

swuehl — Thu, 01 Oct 2015 22:49:32 GMT

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,

Re: Converting multiple date formats

Thu, 01 Oct 2015 23:06:39 GMT

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