you do not necessarily need to convert string to number, it is sufficient to use number interpretation function, Interval#(Start, 'hh:mm') in your case. As for IntervalMatch, I believe you need to provide to fields when loading. In fact, I'd do it in oposite direction compared to what you do. Plus, IntervalMatch takes a field name as an argument, therefore I would avoid using the same field name in StartTable and TimeGroup table. Having loaded the StartTable with Start field and TimeGroup table with StartTime and EndTime (instead of Start/End, just to avoid common field names), I'd continue as follows:
//attach interval fields to the Start field
Left Join (StartTable)
//attach TimeGroup label to respective intervals
Left Join (StartTable)
LOAD * Resident TimeGroup;
//drop TimeGroup table to clean up
Drop Table TimeGroup;
Hope this helps.
Thanks for taking your time on this.
What I have done now:
In StartTable I have renamed "START" to TimeStart.
Then I made a class-function to make intervals and to create the field TimeGroup (Class("START", 480, 'TimeStart') as TimeGroup,). I also used the replace-function to simplify the text output ('>= ' & REPLACE(CLASS("START", 480, 'TimeStart'), '<= TimeStart <', ' < ') AS TimeGroup)
Then i commented out this and created my intervals with several IFs
IF("START">=0 AND "START" <360,'>= 0 < 360',
IF("START">=360 AND "START" <480,'>= 360 < 480',
IF("START">=480 AND "START" <600,'>= 480 < 600',
IF("START">=600 AND "START" <840,'>= 600 < 840',
IF("START">=840 AND "START" <1080,'>= 840 < 1080',
IF("START">=1080 AND "START" <1440,'>= 1080 < 1440')))))) as TimeGroup,
When I was done here, I created my TimeGroup table
LOAD * INLINE [
Start1, Stop1, TimeGroup
0, 360, >= 0 < 360
360, 480, >= 360 < 480
480, 600, >= 480 < 600
600, 840, >= 600 < 840
840, 1000 >= 840 < 1000
1000, 1440 >= 1000 < 1440
No problems yet but now I meet the cavalry:
I get a circular reference and a synthetic key when I load the script below.
When I add the Left Join (TimeGroup) under IntervalMatch the circular reference disappeard, but the synthetic key is still there. Even after i drop the fields Start1 and Stop1.
So my first challenge is to remove the synthetic key and then my challenges are
How to get the minutes into HH:MM when i vizualize and som set-analysis-expression.
Thanks in advance!
I've got lost here. You create your TimeGroup via nested IFs - which is OK - and at the same time you create yet another TimeGroup inline which you want then join via IntervalMatch?
Here's some very simple (but working) example for IntevalMatch use which may help you:
Tmp_Intervals: LOAD * Inline [ ID, Start, End A, 6, 8 B, 2, 15 C, 9, 20 D, 1, 8 E, 8, 15 F, 10, 15 G, 6, 9 H, 8, 9 ]; Elements: LOAD IterNo() as Element AutoGenerate 1 While IterNo()<20; Intervals: IntervalMatch(Element) LOAD "Start", "End" Resident Tmp_Intervals; Left Join(Intervals) LOAD ID, "Start", "End" Resident Tmp_Intervals; Drop Table Elements;
Getting minutes to hh:mm format is fairly simple. You only need to get the that number to a fraction of 1. So 360 minutes would be 360/60/24=0,25 (integer part=date/number of days, decimal part = time), so you can do Inteval(START/60/24, 'hh:mm').
Hope this helps.
Sorry for confusing you, I have only worked with Qlik since two months ago.
My goal is to create vizualizations where i can show how many started in the slots.
For example between 06:00-08:00 (360 - 480 min) started x people.
If you can see my picture I got my timeslots and how many ppl started in each timeslot.
If I understand you correctly - I do not need to create the TimeGroup Inline at all?
Thanks for your patience here!
Example.png 15.2 K
in this case you do not need inline time groups and IntervalMatch at all. See attached app.
BUT, you could use inline definition of time groups and IntervalMatch, because i think it's easier for maintainance than multiple nested IFs. Of course, depends on use case, data volume, etc.
TimeSlots - community.qvf 208.0 K