Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
mszuberl
New 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

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

View solution in original post

6 Replies
Highlighted
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

Highlighted
agni_gold
Valued Contributor III

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

View solution in original post

Highlighted
mszuberl
New Contributor III

Re: Time stamp transformation

This is great! Thank you!

Highlighted
mszuberl
New Contributor III

Re: Time stamp transformation

Thank you for your help!!

Highlighted

Re: Time stamp transformation

you're welcome

thanks

regards

Marco