Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have the following table and i need a timeslot column.
Table:
Trans Number | Time |
1 | 9:00 |
2 | 9:15 |
9 | 8:17 |
11 | 10:22 |
18 | 12:00 |
20 | 9:01 |
3 | 10:01 |
4 | 8:09 |
output Required:
Trans Number | Time | Time Slot |
1 | 9:00 | 9:00-9:30 |
2 | 9:15 | 9:00-9:30 |
9 | 8:17 | 8:00-8:30 |
11 | 10:22 | 10:00-10:30 |
18 | 12:00 | 12:00-12:30 |
20 | 9:01 | 9:00-9:30 |
3 | 10:31 | 10:30-11:00 |
4 | 8:59 | 8:30-9:00 |
Kindly suggest.
Something like this:
Let vEpsilon = Pow(2,-27);
LOAD
[Trans Number],
Time,
Time(floor(Time + $(vEpsilon),1/48),'h:mm') & '-' & Time(ceil(Time + $(vEpsilon),1/48),'h:mm') as [Time Slot]
FROM ...
Hi,
assuming that the two last rows in your output tables include typos one solution could be:
tabTransactTimes:
LOAD @1 as [Trans Number],
@2 as Time,
Dual(Time(SubField(Class(@2,'00:30'),'<=',1),'hh:mm')&'-'&Time(SubField(Class(@2,'00:30'),'x <',2),'hh:mm'),Class(@2,'00:30')) as [Time Slot]
FROM [https://community.qlik.com/thread/169010] (html, codepage is 1252, no labels, table is @1, filters(Remove(Row, Pos(Top, 1))));
hope this helps
regards
Marco
Please close your thread if your question is answered.
Thanks
Regards
Marco
Hi MArco,
I tried similar code for the 2hrs interval for timeslot it dint seem to work.And the input field is DateTime.
Can you help me how to acheive the same.Attached is the change in interval
Hi MArco
Attach is the qvw which i tried with my file and the expectation image
Thanks
Thiru
source file attached here
qvw mised to attached in the previous thread