topic Re: Date format in QlikView

Date format

srinivasa1 — Fri, 23 Sep 2016 22:07:34 GMT

Hi can any once guide how I can make datekey from below date.

Date

6/23/2014 1:59:56 AM

10/14/2014 3:18:05 PM

10/14/2014 4:05:29 PM

10/14/2014 4:11:25 PM

10/14/2014 4:13:16 PM

10/14/2014 4:23:43 PM

I wanted in below datekey format

20140623

20141014

Thanks for any help.

Re: Date format

swuehl — Fri, 23 Sep 2016 22:20:03 GMT

Try a combination of Timestamp#() interpretation, Date() formatting and Num#() interpretation:

=Num#(Date(Timestamp#('6/23/2014 1:59:56 AM','M/D/YYYY h:m:s TT'),'YYYYMMDD'))

LOAD

Date,

Num#(Date(Timestamp#(Date,'M/D/YYYY h:m:s TT'),'YYYYMMDD')) as DateKey,

...

Re: Date format

sunny_talwar — Fri, 23 Sep 2016 22:23:29 GMT

You can also use try this if the date key represents date rather than a number:

Date(Date#(SubField(Date, ' ', 1), 'M/D/YYYY'), 'YYYYMMDD') as DateKey

Re: Date format

el_aprendiz111 — Fri, 23 Sep 2016 22:36:37 GMT

2-options:

1) DATE([Date],'YYYYMMDD') AS datekey

2) DATE(Floor([DueDate]),'YYYYMMDD') AS datekey

Re: Date format

srinivasa1 — Fri, 23 Sep 2016 22:38:34 GMT

Wow swuehl. Perfect!!!.

Thanks@

Re: Date format

srinivasa1 — Fri, 23 Sep 2016 23:09:39 GMT

Thanks Sunny T. your also working!.

Re: Date format

Digvijay_Singh — Sat, 24 Sep 2016 06:19:07 GMT

Hi Stefan,

Will this calculated DateKey contain decimal part as well? Wouldn't it create problem in association, I was thinking if we should use floor to remove decimal part from number representation in case we use this field for association.

Thanks,

Re: Date format

trdandamudi — Sat, 24 Sep 2016 06:40:59 GMT

You can also try the below:

Date(Floor(Date),'YYYYMMDD') as DateKey

Re: Date format

swuehl — Sat, 24 Sep 2016 08:48:46 GMT

No, the decimal part is stripped off by using Num#() interpretation function, interpreting a six digit integer number format text value like 20140623. Num# will set the numeric representation of your dual accordingly.

That's the whole purpose I am using this function.

Re: Date format

Digvijay_Singh — Sat, 24 Sep 2016 11:20:55 GMT

Oh yeah, missed the point that Num#() will take text representation of the calculated value before its application.

Thanks for clarifying!

Regards,