topic Re: Unix Dates in QlikView

Unix Dates

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a field which when loaded into qlik appears as a Unix date. To overcome this, I wrote the following:

(Date(((NoteValue / 1000) /86400) + 25569))

However I noticed in the raw data that I was getting a handful of dates that were 01/01/1970.

I looked at the main front application of the system this data is entered into, and it appears below are entering 2 formats. Some are entering it as 25 Jan 2016 and some are entering it as 25-01-2016.

Therefore I wrote this expression, but it doesnt notice the first if criteria.

if ((Date(((NoteValue / 1000) /86400) + 25569))='01/01/1970', NoteValue,(Date(((NoteValue / 1000) /86400) + 25569))) as rep_S2,

Any ideas?

Thank you.

Re: Unix Dates

Gysbert_Wassenaar — Fri, 11 Mar 2016 10:40:06 GMT

Usually one uses something like this to turn a unix timestamp into a date:

If(unixtime,TimeStamp(Makedate(1970,1,1)+ unixtime /24/60/60))

If you need to convert multiple sort of date strings into a date you can try something like

Alt( date#(MyDate, 'DD-MM-YYYY'),

Alt(date#(MyDate, 'DD MMM YYYY'),

Alt(...etc))) as MyDate

Re: Unix Dates

Fri, 11 Mar 2016 10:40:08 GMT

Can you post sample code of the application to see...

Re: Unix Dates

Fri, 11 Mar 2016 11:07:02 GMT

Thanks.

No this hasnt worked. I added the following:

If(unixtime,TimeStamp(Makedate(1970,1,1)+ NoteValue /24/60/60))

and the 01/01/1970 dates still appear. And then the Unix dates are appearing as 16892569

Thanks.

Re: Unix Dates

Gysbert_Wassenaar — Fri, 11 Mar 2016 11:13:23 GMT

Please post a small qlikview document with sample data that demonstrates the problem.