topic Autogenerate timestamps, based on input from Excel in QlikView

Autogenerate timestamps, based on input from Excel

clausbarlose — Fri, 08 Jan 2016 15:33:00 GMT

Happy New Year to all!

I want to autogenerate timestamps based on input from an Excel sheet.

In short, purpose is to decide whether timestamps from another table, collides in time with periods in the Excel sheet.

Autogenerated timestamps must be in five minutes intervals.

Excel input file layout:

StartTimestamp, EndTimestamp, Comment

e.g.

2015-01-12 14:00:00, 2015-01-14 17:00:00, Relase #101515

2015-02-25 23:00:00, 2015-02-26 03:00:00, Release #224355

...

Hopefully someone has tried this before?

Best regards,

Claus

Re: Autogenerate timestamps, based on input from Excel

Gysbert_Wassenaar — Fri, 08 Jan 2016 19:08:18 GMT

Something like this:

LOAD

StartTimestamp,

TimeStamp(StartTimestamp+(IterNo()-1)/24/12) as TimeStamp

EndTimestamp,

Comment

FROM

MyExcelFile.xlsx (ooxlm, ...etc)

WHILE

StartTimestamp+(IterNo()-1)/24/12 <= EndTimestamp

;

If your excel timestamps aren't timestamps but text strings then you first need to use the timestamp# function to turn the strings into numeric timestamp values: timestamp#(StartTimestamp,'YYYY-MM-DD hh:mm:ss') as StartTimestamp

Re: Autogenerate timestamps, based on input from Excel

clausbarlose — Sat, 09 Jan 2016 11:51:10 GMT

Hi Gysbert

Thank you very much - works exactly as it I wanted!

Best regards

Claus