How to populate a sparsely populated field

[hic]

Sometimes when you load data into QlikView you find that a field is sparsely populated, i.e. it has discrete enumerable values where some values are missing.

It could be like in the table to the right where you have three dates, each with some kind of conversion rate. The table only contains the dates where the conversion rate changed, not the dates between the changes.

However, the user will want to ask the question: “What was the status on this specific day?” In other words, the user wants to be able to click on a reference date to see the number that is associated with this date – but the date might not exist in the source data.

In such a situation, you need to generate the missing dates between the changes as individual records and use the “Rate” value from the previous date.

There are several ways to do this in QlikView, and all of them involve some script programming, using temporary tables. One algorithm is

1. Load the source table containing the rates (below called “Rates”).
2. Find largest and smallest date in the “Rates” table.
3. Generate all dates between the largest and smallest dates (below called “Dates”)..
4. Join the “Dates” table (outer join) onto the “Rates” table.
5. Sort the resulting table according to date.
6. Propagate the value of “Rate” downwards to all records that have NULL in the “Rate” field, using the Peek() function.

Visually, the join and peek steps of the algorithm look like this:

In the QlikView script, the algorithm would look like the following:

     TempTable_Rates:
     Load Date, Rate From Rates ;

     MinMaxDate:
     Load Min (Date ) as MinDate , Max (Date ) as MaxDate resident TempTable_Rates;
     Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
     Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;

     Join (TempTable_Rates)

     Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;

     Rates:

     NoConcatenate Load Date,
          If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate
          Resident TempTable_Rates
          Order By Date ; /* so that above values can be propagated downwards */

     Drop Table MinMaxDate, TempTable_Rates;

Problem solved!

This method can be adapted for most situations when you need to insert additional records in existing data: Warehouse balances, Exchange rates, etc.

See more in the Technical Brief: Generating Missing Data In QlikView.

HIC

[annabergendal]

Thanks! Now it's working!

[qlikviewwizard]

Good blog HIC. Thanks for sharing.

[hansdevr]

I couldn't get it to work for our situation. I think that is because the same problem occurs for a lot of situations in a single table. Let me explain: in my situation it's about an insurance company that wishes to keep track of reserve status totals AND reserve per customer file. My table looks something like the one I put below. Now suppose the question would be: what is the reserve for all filenrs at a given reference date?

Let's say the reference date would be feb 4th, 2014... Of course we don't have three files, but 100,000s..

crdate= creation date of that record (follows a payment to customer automatically);

linenr= nr of the created line;

rnk= ranking of the reserve status. All rankings are re-numbered each time a new line is added, so rnk = 1 represents the most current reserve status.

reserve= the mount of reserve for that particular customer file.

Any help would be very welcome!

filenr	crdate	linenr	rnk	reserve
100001	2-1-2014	1	4	15000
100001	8-1-2014	2	3	10000
100001	16-1-2014	3	2	5000
100001	28-1-2014	4	1	0
110001	15-1-2014	1	6	20000
110001	3-2-2014	2	5	18000
110001	5-2-2014	3	4	15000
110001	10-2-2014	4	3	12000
110001	14-2-2014	5	2	9000
110001	1-3-2014	6	1	6000
140002	15-1-2014	1	3	11000
140002	1-2-2014	2	2	8000
140002	5-2-2014	3	1	5000

[hic]

One solution could be that you generate all combinations of files and dates (which could lead to a very large file) and use the above algorithm:

tmpData:
LOAD Date#(crdate,'D-M-YYYY') as crdate,
filenr, linenr, rnk, reserve
FROM Data.txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MinMaxDate:
Load Min(crdate) as MinDate, Max(crdate) as MaxDate resident tmpData;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;
Drop Table MinMaxDate;

CartesianProduct:
Load distinct filenr Resident tmpData;
Join (CartesianProduct)
Load Date(recno()+$(vMinDate),'D-M-YYYY') as crdate Autogenerate vMaxDate - vMinDate;

Right Join (tmpData)
Load * Resident CartesianProduct;
Drop Table CartesianProduct;

Data:
Load crdate,
reserve as original_reserve,
filenr, linenr, rnk,
If(Peek(filenr)=filenr,
If(IsNum(reserve), reserve, Peek(reserve)),
RangeSum(reserve)) as reserve
Resident tmpData Order By filenr, crdate;
Drop Table tmpData;

The total reserve is then simply Sum(reserve)

HIC

[hansdevr]

Dear Henric,

Though your solution did indeed work, it lead to a ridicously huge table. After a lot of thought and a "little help from my friend" (read: a lot of help) I decided to go for his solution, in which the original table stayed at exactly the same size.

It all boils down to adding a new field to the record (end_date), which indicates the "period of useability" of that record.

Step 1: read all records from the table into a temporary table.

Step 2: Make a new table, containing all fields and values from the temporary table with addition of a new field end_date. Sort this table by filenr and crdate, descending. Now fill the value of end_date by using an if-clause. IF the filenr of the previous record = the filenr of the current record then the end_date equals today (one time only), and the end_date of the previous record equals crdate of the current record minus 1.

Doing this, you create a period of useability for all records.

Step 3: drop the original, temporary table.

Totaling the reserve is just a sum of all records that meet the condition crdate<= reference date<= end_date.

The script looks like this (I don't know how to paste scripts like you do):

Reserveringen_Temp:
LOAD
     filenr,
     crdate,
     reserve
FROM

(ooxml, embedded labels, table is Sheet1);

Reserveringen:
LOAD
*,
[crdate]        AS Startdatum,
Date(if(filenr=previous(filenr),
  previous(crdate)-1,
  Date(Floor(Today()))))      AS Einddatum
Resident Reserveringen_Temp
ORDER BY filenr, crdate DESC
;

// Opschonen tijdelijke tabel

Drop Table Reserveringen_Temp;

MasterCalendar:
LOAD
  Datum,
  Year(Datum)           AS Jaar,
  Month(Datum)           AS Maand
;
LOAD
  Date(EersteDatum + IterNo())       AS Datum,
  LaatsteDatum
WHILE EersteDatum + IterNo() <= LaatsteDatum
;
LOAD
  Min(crdate)-1   AS EersteDatum,
  Floor(Today())    AS LaatsteDatum
RESIDENT Reserveringen;

I made totals like this:

=Money(sum( if( Startdatum<= vDatum AND Einddatum>= vDatum, [In €],0) ))

[#7]

Thanks Henric Cronstrom for the nice post.

could you please help me on the following issue,

I have Unit , Date, Accumulated mileage  value for a vehicle , the vehicle may not have continues dates, however I need all the dates and its accumulated values if vehicle has the value in the source data we need to pick that or else need to pick from its previous date value, each vehicle has its own minimum dates. I have totally 50,000 vehicles the dates are started from 2005. how can I apply the above method to my issue could you please help me on this.

Populate missing months

Regards

John ! 

[roland74]

Hi,

Great post!

I'm looking for something similar combined with the AsOfPeriod example from this post Calculating rolling n-period totals, averages or other aggregations

I have created a cumulative expression for our stock quantity (per item) with the rollingtodate example from the post mentioned above (my period is YYYYMM) but now I have to calculate the stock's value....

I have created a query that gives me the price per item per period (YYYMM) but how do I merge these prices with the quantity and how do I populate the prices per item per period (because a price has no end date)?

Thanks a lot!

Roland

[Lauri]

I had a small wrinkle on this that might be applicable to others: I need to report on the full range of dates for a dozen different organizations. So there is an additional column, ORGID in my table. I added a For... Each statement around the Load statement that generates the dates. It draws its values from a pre-loaded table that contains ORGID. I also store the list of ORGID + dates in a new table so that I can reuse it many times in subsequent Load statements. I drop it at the end.

FOR Each a in FieldValueList('ORGID')

    DateRange:

    LOAD '$(a)' as ORGID, Date(recno()+$(vMinDate)) as TheDate Autogenerate vMaxDate - vMinDate;

NEXT a;

[Anonymous]

I have a slightly different requirement.I have been able to populate Ticket numbers with '0' for the missing months. But this field needs to link with the Application filter box. i.e.i need the application to be mentioned for blank ticket number for the generated missing dates. I have 10 such applications and these 10 application names should be repeated in separate rows with '0' Ticket number for the same date.

i.e.

Application Ticket number Date

A                0                      4/4/2017

B                0                      4/4/2017

C               0                       4/4/2017

.

.

.

..

[edvard_poldrugo]

Excellent post, thanks!