Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mszuberl
Contributor III
Contributor III

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

1 Solution

Accepted Solutions
MarcoWedel

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

View solution in original post

6 Replies
maxgro
MVP
MVP

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
Specialist III
Specialist III

Please provide some sample data .

MarcoWedel

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
Contributor III
Contributor III
Author

This is great! Thank you!

mszuberl
Contributor III
Contributor III
Author

Thank you for your help!!

MarcoWedel

you're welcome

thanks

regards

Marco