Discussion board where members can get started with Qlik Sense.
Jan 02 2018
Feb 05 2018
I have date in different formats, I wants the result in number format ex: (43210), I tried it in different ways
(Date#(Date("DATE",'DD/MM/YYYY') and Date("DATE",'DD/MM/YYYY') but didn't got expected result.
Can anyone help me to get it?
Date(Alt(Num(Date#(DATE, 'MM-DD-YYYY')), Num(Date#(DATE, 'MMM DD YYYY'))), 'DD/MM/YYYY') as DATE
You can give in each format into below code and try
alt( date#( dat , 'YYYY/MM/DD' ),
date#( dat , 'MM/DD/YYYY' ),
date#( dat , 'MM/DD/YY' ),
'No valid date' ) as newDate
You can try the same script as above with adding num function in the beginning to convert into num format.
num(alt( date#(date,'MMM DD YYYY'),
'No valid date')) as newDate
if you want to convert everything into particular date format try
date(alt( date#(date,'MMM DD YYYY'),
'No valid date'),'your date format') as newDate
What do you mean not work???
Solution suggested by sunny must work...
As he is looking for number format
Date#(Alt(Num(Date#(DATE, 'MM-DD-YYYY')), Num(Date#(DATE, 'MMM DD YYYY'))), 'DD/MM/YYYY') as DATE
I think OP might have changed the requirement at a later time... initially he wanted the output in DD/MM/YYYY format...
I did Sunny Talwar example's and worked perfectly.
I Attached a app with this example.
I note that have a date "02-29-2017", but how february don't have 29 day the qlik changed to "03-01-2017".
Note i did two diferents data formats.
And i use floor command to convert to number.