Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mszuberl
New Contributor II

Time stamp transformation

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.

1.PNG

2.PNG

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Time stamp transformation

Hi,

another solution could be:

QlikCommunity_Thread_198109_Pic1.JPG

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

6 Replies
MVP
MVP

Re: Time stamp transformation

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;

1.png

agni_gold
Valued Contributor

Re: Time stamp transformation

Please provide some sample data .

Highlighted

Re: Time stamp transformation

Hi,

another solution could be:

QlikCommunity_Thread_198109_Pic1.JPG

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

mszuberl
New Contributor II

Re: Time stamp transformation

This is great! Thank you!

mszuberl
New Contributor II

Re: Time stamp transformation

Thank you for your help!!

Re: Time stamp transformation

you're welcome

thanks

regards

Marco

Community Browser