Unix Dates

[#1]

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.

[Gysbert_Wassenaar]

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

[#3]

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

[#4]

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.

[Gysbert_Wassenaar]

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