Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a data source where tha date and time is under unix format (timstamp: number of seconds since 1970/01/01 00:00:000).
I need to transform this in ASCII timestamp/ Windows Standard. I cannot see a QlikView Function to do this. I can use DADEDIFF when with SQL but I still need to have the result also for csv data sources.
Is anyone aware of a QlikView built-in function? Or maybe a VBScript to calculate?
Thanks in advance,
Regards,
John
John,
if Unix format is number of seconds since 1970/01/01, and QlikView format is number of days since 1900/01/01,it's rather simple to calculate:
QlikView date for 1970/01/01 is 25569. From here, add the number of days based on the Unix Date:
QV Date = 25569 + <UnixDate> / 24 hours / 60 minutes / 60 seconds
This formula might be off by a day, play with it before implementing.
cheers,
Oleg
John,
if Unix format is number of seconds since 1970/01/01, and QlikView format is number of days since 1900/01/01,it's rather simple to calculate:
QlikView date for 1970/01/01 is 25569. From here, add the number of days based on the Unix Date:
QV Date = 25569 + <UnixDate> / 24 hours / 60 minutes / 60 seconds
This formula might be off by a day, play with it before implementing.
cheers,
Oleg
By the way, I don't recommend implementing this formula in VBScript function (even though it looks cool) - it will slow things down a lot. Once you nail the exact syntax of the formula, either copy it everywhere you need, or use $() - extensions to encapsulate the logic.
cheers,
Oleg
thanks a lot Oleg. It is really simple indeed and logic.
but I didn't think about it...
Thanks again,
Regards,
John