topic Re: Convert String to Date and break down into Day, Month, Year in QlikView

Convert String to Date and break down into Day, Month, Year

stjernvd — Wed, 09 Sep 2015 16:12:02 GMT

Hi,

I am having trouble converting my date string into a proper qlikview recognizable date, these are my dates:

20110418

20150312

20150819

20150827

20150430

20150223

20150729

I used this in the script to convert them to a more readable date:

Date(Date#(date,'YYYYMMDD'),'DD-MM-YYYY') as [Date]

and I got this:

18-04-2011

12-03-2015

and so on...

However, when I want to create a day, month, year dimension to have as list boxes using this function it doesn't work:

Month([Date]) as Month,

In my list box the month doesn't match up to the date in the table...when I press June in my list box, the June dates are not filtered for in my bigger table.

How do I fix this?

Re: Convert String to Date and break down into Day, Month, Year

ger_alegria — Wed, 09 Sep 2015 16:23:23 GMT

Try with:

Month(Date(Date#([Last Activity Date],'YYYYMMDD'),'DD-MM-YYYY') ) as Month

Day(Date(Date#([Last Activity Date],'YYYYMMDD'),'DD-MM-YYYY') ) as Day

Year(Date(Date#([Last Activity Date],'YYYYMMDD'),'DD-MM-YYYY') ) as Year

Let me Know if it works.

Ger Alegría.

Re: Convert String to Date and break down into Day, Month, Year

stjernvd — Wed, 09 Sep 2015 16:28:55 GMT

It works thank you so much for your help, I was struggling with this for a while!!

Re: Convert String to Date and break down into Day, Month, Year

MarcoWedel — Wed, 09 Sep 2015 17:48:32 GMT

move your line

Month([Last Activity Date]) as Month,

to a preceding load

regards

Marco