Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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;
Please provide some sample data .
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
This is great! Thank you!
Thank you for your help!!
you're welcome
thanks
regards
Marco