Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi to all,
I have an excel file with 3 coloumns. The first is the starting value, the second is the final value and the third is a flag (1 or 2) that characterizes all the values in the (final - starting) value range.
eg
start finish flag
1 5 D
6 8 D
10 17 C
etc.
I have in my data a field that i want to find out during load time, in which value range it resides so as to add the D or C flag of the corresponding range.
Any help would be greately appreciated.
Thank you all in advance.
You can look into using the INTERVALMATCH LOAD prefix (there is a chapter about that in the HELP / reference manual, or search the forum / blogs).
You can look into using the INTERVALMATCH LOAD prefix (there is a chapter about that in the HELP / reference manual, or search the forum / blogs).
Try INTERVELMATCH function. Please find the below from Help File:
Example:
In the two tables below, the first one defines the start and end times for the production of different orders. The second one lists a number of discrete events. By means of the IntervalMatch prefix it is possible to logically connect the two tables in order to find out e.g. which orders were affected by disturbances and which orders were processed by which shifts.
OrderLog | ||
Start | End | Order |
01:00 | 03:35 | A |
02:30 | 07:58 | B |
03:04 | 10:27 | C |
07:23 | 11:43 | D |
EventLog | ||
Time | Event | Comment |
00:00 | 0 | Start of shift 1 |
01:18 | 1 | Line stop |
02:23 | 2 | Line restart 50% |
04:15 | 3 | Line speed 100% |
08:00 | 4 | Start of shift 2 |
11:43 | 5 | End of production |
First load the two tables as usual, then link the field Time to the time intervals defined by the fields Start and End:
SQL SELECT * FROM OrderLog;
SQL SELECT * FROM Eventlog;
IntervalMatch ( Time ) SQL SELECT Start, End FROM OrderLog;
The following table box can now be created in QlikView:
Tablebox | |||||
Time | Event | Comment | Order | Start | End |
00:00 | 0 | Start of shift 1 | - | - | - |
01:18 | 1 | Line stop | A | 01:00 | 03:35 |
02:23 | 2 | Line restart 50% | A | 01:00 | 03:35 |
04:15 | 3 | Line speed 100% | B | 02:30 | 07:58 |
04:15 | 3 | Line speed 100% | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | C | 03:04 | 10:27 |
08:00 | 4 | Start of shift 2 | D | 07:23 | 11:43 |
11:43 | 5 | End of production | D | 07:23 | 11:43 |
Thank you both for your quick replies.
I looked into the IntervalMatch function and it worked great for me!
Also the example from dathu.qv was very helpfull.
Thanks again!