topic Date transformation in QlikView

Date transformation

mario-sarkis — Fri, 11 Sep 2015 08:09:26 GMT

Hey All

if i have a filed Date look like this for example '20150831' how can tranform this into a number ?

Thank you, hope you can help.

Re: Date transformation

sunny_talwar — Fri, 11 Sep 2015 08:11:30 GMT

Try this:

Num(Date#('20150831', 'YYYYMMDD'))

For a whole field to be converted, try this:

Num(Date#(DateField, 'YYYYMMDD'))

Re: Date transformation

qlikmsg4u — Fri, 11 Sep 2015 08:22:30 GMT

For Number Format : Num(Date#('20150831', 'YYYYMMDD'))

For Date Format : Date(Date#('20150831', 'YYYYMMDD'),'MM/DD/YYYY')

Re: Date transformation

Fri, 11 Sep 2015 08:22:42 GMT

Hey Sunny,

I just tried this in a text box and am not getting any result.

I used the expression:

=Num(Date#(today(), 'YYYYMMDD'))

and the output am getting is: - (hyphen)

Re: Date transformation

kamalqlik — Fri, 11 Sep 2015 08:28:46 GMT

Hi Gergy,

Please find the attached application for your refrence

REgards

Kamal

Re: Date transformation

hic — Fri, 11 Sep 2015 08:43:54 GMT

Date(Date#(DateField, 'YYYYMMDD'),'YYYY-MM-DD')

See QlikView Date fields

HIC

Re: Date transformation

MarcoWedel — Fri, 11 Sep 2015 17:56:53 GMT

H‌i,

20150831 already is a number, isn't it?

Please specify if it's not quite the number you are looking for.

thanks

regards

Marco

Re: Date transformation

sunny_talwar — Fri, 11 Sep 2015 19:48:55 GMT

Since your date format must be set differently, your Today() won't be in YYYYMMDD format. When you use Date# and specify that Today is in YYYYMMDD you get null(). But mario Gergy-sarkis‌ specified that his date is in YYYYMMDD format, so I am telling QlikView to understand his date format by using the Date#() function.

In short, you can just do this and it will work:

=Num(Today())

Re: Date transformation

qlikviewwizard — Sat, 12 Sep 2015 02:18:49 GMT

Hi,

What you are going to do after converting the number again to number?

Do want to use in where clause?