Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi folks,
I want create a time range dimension to have the following values in 30 minutes range:
7:00 AM
7:30 AM
8:00 AM
8:30 AM
until 11:30 PM
So I have a time field that shows for example
07:15 AM
3:23 PM
4:54 PM
If I select 7:15AM I should see this date under the 7:00 to 7:30am slot.
I created an inline like this but it is not working when I do the intervalmatch with the time field from my database.
LOAD * INLINE [
Start, End, Slot
07:00, 07:29, 7:00 AM
07:30, 07:59, 7:30 AM
08:00, 08:29, 8:00 AM
];
It looks like the interval match works with numbers but when I use time it doesn't work. Any suggestion of what is the formatting to use?
in order to get all time ranges of 30min length within a day, you could create those values in a seperate table:
tabData:
LOAD *,
Dual(Time(SubField(Class([time field],1/48),' <= x < ', 1))&' - '&Time(SubField(Class([time field],1/48),' <= x < ', 2)), Class([time field],1/48)) as [time range]
Inline [
time field
07:15 AM
3:23 PM
4:54 PM
];
tabTimeRange:
LOAD Dual(Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 1))&' - '&Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 2)), Class((IterNo()-1)/48,1/48)) as [time range]
AutoGenerate 1
While IterNo()<=48;
hope this helps
regards
Marco
You can also use the Class() function in this case
Thank you, but the class() function won't work, since I want my dimension to specifically show slots of 30 minutes.
or you can add a column in your script (t is your field)
load
t,
'from ' & time(floor(Time#(t, 'hh:mm tt'),1/48), 'hh:mm') &' to ' & time(floor(Time#(t, 'hh:mm tt'),1/48) + 1/48, 'hh:mm') as slot
....
This is not working from me, could you post your example please?
IntervalMatch should work with time format, but you 'll need to make sure that the field you are using are time and not string. All times are fractions.
Load
Time#('Start', 'hh:mm') as Start,
Time#('End', 'hh:mm') as End,
Slot
INLINE [
Start, End, Slot
07:00, 07:29, 7:00 AM
07:30, 07:59, 7:30 AM
08:00, 08:29, 8:00 AM
];
ok
Hi,
is this what you are looking for?:
LOAD *,
Dual(Time(SubField(Class([time field],1/48),' <= x < ', 1))&' - '&Time(SubField(Class([time field],1/48),' <= x < ', 2)), Class([time field],1/48)) as [time range]
Inline [
time field
07:15 AM
3:23 PM
4:54 PM
];
hope this helps
regards
Marco
in order to get all time ranges of 30min length within a day, you could create those values in a seperate table:
tabData:
LOAD *,
Dual(Time(SubField(Class([time field],1/48),' <= x < ', 1))&' - '&Time(SubField(Class([time field],1/48),' <= x < ', 2)), Class([time field],1/48)) as [time range]
Inline [
time field
07:15 AM
3:23 PM
4:54 PM
];
tabTimeRange:
LOAD Dual(Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 1))&' - '&Time(SubField(Class((IterNo()-1)/48,1/48),' <= x < ', 2)), Class((IterNo()-1)/48,1/48)) as [time range]
AutoGenerate 1
While IterNo()<=48;
hope this helps
regards
Marco
another solution using intervalmatch() instead of class() could be:
tabData:
LOAD * Inline [
time field
07:15 AM
3:23 PM
4:54 PM
];
tabTimeRange:
LOAD *,
Dual(Start&' - '&End, Start) as [time range];
LOAD Time((IterNo()-1)/48) as Start,
Time(IterNo()/48-1/86400000) as End
AutoGenerate 1
While IterNo()<=48;
tabLink:
IntervalMatch ([time field])
LOAD Start, End
Resident tabTimeRange;
hope this helps
regards
Marco