topic Re: Time stamp transformation in QlikView

Time stamp transformation

mszuberl — Thu, 31 Dec 2015 21:51:11 GMT

Hi and Happy New Year (almost)!

I have a question, I hope, someone will be able to help me with.

I am working with a table of data, please see attached data sample, which I need to transform (preferable, via script) to look like the second attachment.

Basically, I need to be able to figure out, whether the site was open during currently selected hours of the day, and the number of minutes the site was open during that time.

Any help is very appreciated!

Marcin

Re: Time stamp transformation

maxgro — Fri, 01 Jan 2016 03:43:49 GMT

try this (you have to make some change for the non full hours, ie 23:59, 16:30 etc.....) in the if

SET TimeFormat='hh:mm:ss';

INPUT:

load * Inline [

key_CustomerSiteHours,Site,ScannerType,WeekDay,Open,Close

26,10_2,CT,Monday,00:00:00,23:59:00

27,10_2,CT,Tuesday,00:00:00,23:59:00

28,10_2,CT,Wednesday,00:00:00,23:59:00

29,10_2,CT,Thursday,00:00:00,23:59:00

30,10_2,CT,Friday,00:00:00,23:59:00

31,10_2,CT,Saturday,00:00:00,23:59:00

32,10_2,CT,Sunday,00:00:00,23:59:00

33,10_2,US,Monday,00:00:00,23:59:00

34,10_2,US,Tuesday,00:00:00,23:59:00

35,10_2,US,Wednesday,00:00:00,23:59:00

36,10_2,US,Thursday,00:00:00,23:59:00

37,10_2,US,Friday,00:00:00,23:59:00

38,10_2,US,Saturday,00:00:00,23:59:00

39,10_2,US,Sunday,00:00:00,23:59:00

47,10_2,MR,Sunday,06:00:00,22:00:00

54,10_2,NM,Sunday,06:00:00,16:30:00

];

For i = 0 to 23

left join (INPUT) LOAD

key_CustomerSiteHours,

if(Time#($(i), 'h')>=Time#(Open) and Time#($(i), 'h')<=Time#(Close),

Time(Time#(1,'h'),'h:mm'),

Time(Time#(0,'h'),'h:mm')) as [$(i):00]

Resident INPUT;

Next;

Re: Time stamp transformation

agni_gold — Fri, 01 Jan 2016 10:28:16 GMT

Please provide some sample data .

Re: Time stamp transformation

MarcoWedel — Sat, 02 Jan 2016 20:44:43 GMT

Hi,

another solution could be:

table1:
LOAD RecNo() as key, *
Inline [
ID, Weekday, SiteOpeningTime, SiteClosingTime
1, Monday,    00:00:00, 23:59:00
1, Tuesday,  02:10:00, 23:30:00
1, Wednesday, 03:20:00, 22:00:00
1, Thursday,  04:30:00, 21:40:00
1, Friday,    07:40:00, 20:20:00
1, Saturday,  12:50:00, 19:30:00
1, Sunday,    15:00:00, 18:10:00
2, Monday,    00:00:00, 23:59:00
2, Tuesday,  01:20:00, 22:49:00
2, Wednesday, 02:30:00, 21:39:00
2, Thursday,  03:40:00, 20:29:00
2, Friday,    04:50:00, 19:19:00
2, Saturday,  06:00:00, 18:09:00
2, Sunday,    07:10:00, 17:00:00
];

table2:
LOAD key,
    Hour,
    Interval#(Interval(RangeMax(RangeMin(SiteClosingTime,Hour+'01:00')-RangeMax(SiteOpeningTime,Hour),0),'hh:mm'),'hh:mm') as TimeOpen;
LOAD key,
    Time((IterNo()-1)*'01:00','hh:mm') as Hour,
    SiteOpeningTime,
    SiteClosingTime
Resident table1
While IterNo()<=24;

hope this helps

regards

Marco

Re: Time stamp transformation

mszuberl — Fri, 08 Jan 2016 18:43:14 GMT

This is great! Thank you!

Re: Time stamp transformation

mszuberl — Fri, 08 Jan 2016 18:43:59 GMT

Thank you for your help!!

Re: Time stamp transformation

MarcoWedel — Fri, 08 Jan 2016 23:49:44 GMT

you're welcome

thanks

regards

Marco