Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have a requirement in which we have to populate missing data in our table.
We have table by the name of ShiftData. In there we have the following fields
dos_id_pk, dos_tun_id, nQueryDate, SlotNumber, MovingMinutes, IdleMinutes, EngineOnMinutes, GeoLocations etc. etc.
What we want to do is the following:
for each dos_tun_id
* populate the missing dates - from the first date the record was inserted to Yesterday
* for each date of dos_tun_id enter populate the missing slot number (1,2,3,4,6,7,8)
* have null values or 0 for other columns such as movingminutes, idleminutes, engineonminutes etc.
From the image above - the missing fields will be populated as follows:
6143 - in date 01-01-2015 slots 1,7,8
6143 - missing date 02-01-2015 all slots (1,2,3,4,6,7,8)
Will appreciate if someone can assist.
Thanks in advance.
you can use intervalmatch for this
The IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals.
It must be placed before a Load or Select (SQL)statement that loads the intervals. The field containing the discrete data points (Time in the example below) must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix does not by itself read this field from the database table. The prefix transforms the loaded table of intervals to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point and interval.
The intervals may be overlapping and the discrete values will be linked to all matching intervals.
The general syntax is:
intervalmatch (matchfield) (loadstatement | selectstatement )
matchfield is the field containing the discrete numeric values to be linked to intervals.
loadstatement or selectstatement must result in a two-column table, where the first field contains the lower limit of each interval and the second field contains the upper limit of each interval. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).
There is also an extended syntax of IntervalMatch including one or several additional key fields. See IntervalMatch (Extended Syntax).
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:
OrderLog:
LOAD * INLINE [
Start, End, Order
01:00, 03:35, A
02:30, 07:58, B
03:04, 10:27, C
07:23, 11:43, D
];
EventLog:
LOAD * INLINE [
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
];
IntervalMatch (Time) LOAD Start, End Resident 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 |
PFA
1. cretae a table (Table1) wich would have complete list of all values i.e.
by multiplying date (generate all the dates using autogenerate) , dos_tun_id (distinct/all values/inline or from a table), slot number (distcint/all values , inline or from a table) field together (using join) + your metrics field as 0 (if you want to show them as 0 instead of null)
2. then create key a create on top if this (preceding load or resident load ) for date&dos_tun_id& slot number as Key2
3. Load your fact table, again create a key for date&dos_tun_id& slot number as Key1
4. concatenate Table2 with where clause as not exists(Key1,Key2)