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

[Suus]

Hi Sunil.

I have a similar challenge. Did you solve it? http://community.qlik.com/thread/128328

Regards, Suzan

[sunilkumarqv]

Hi Suzan,

After several attempts. I'm able to crack that .

In My scenario :

as follows  synchronize this script into your scenario based

MinMaxTable:

  Load min(Date) as MinDate,

  Max(Date) as MaxDate

  resident Tablename;

  // floor converts to integer to strip time component

  Let vDateZero =floor(Num(peek('MinDate',0,'MinMaxTable'))) - 1 ;

  Let vLastDate = floor(Num(peek('MaxDate',0,'MinMaxTable'))) ;

  Let vFirstDate = floor(Num(peek('MinDate', 0, 'MinMaxDate'))) ;

  Drop Table MinMaxTable ;

Join(TableName)

  // This load runs 2nd to generate date components

  Load *,

  MonthEnd(Date) as MonthEndDate,

  AutoNumber(MonthEnd(Date)) as MonthEndID,

  year(Date) as Year,

  Month(Date) as Month;

  // This load runs 1st

  Load Date($(vDateZero) + recno()) as Date 

  Autogenerate $(vLastDate) - $(vDateZero);

PLS:

     NoConcatenate Load Date,

          If( IsNull( FieldName ), Peek( FieldName ), FieldName ) as FieldName ,

         If( IsNull( FieldName 2), Peek( FieldName 2), FieldName2 ) as FieldName

           Resident TableName

          Order By Date ; /* so that above values can be propagated downwards */

[#4]

Hi Hendric,

I have read your generating missing data pdf file.We have a situation similar to,  Populating a table with warehouse balances. But in our case we have a three dimension data instead of two. We have date, store and Item. The item price comes from SalesOrder table which only give the price on a particular date. I have tired to adopt your example given for two dimension, but till now not able succeed. We need to populate the price of an item in a store for all the missing dates. The price will be same from the date the salesorder is released to next sales order date. if there is no sales order for the last one month, then we need to populate the last month's price to till date. Any pointers to deal with three dimension is appreciated.

Thanks & Regards

Kichenin

[#5]

Excellent blog just what I was looking for!

[annabergendal]

Hi,

I use your method but end up with a script error:

Error in expression: ')' expected

Join (Qty_Tmp)

     Load Date(recno()+) as MonthYear

     Autogenerate vMaxDatePeriod - vMinDatePeriod;

What could be the problem? Any help is appreciated. The code the cause the failure:

Join (Qty_Tmp)

     Load Date(recno()+$(vMinDatePeriod)) as MonthYear

     Autogenerate vMaxDatePeriod - vMinDatePeriod;

[hic]

You have an unassigned variable in

     Load Date(recno()+$(vMinDatePeriod)) as MonthYear

The plus sign cannot be followed by a right parenthesis.

Run the script in the debugger to find out why vMinDatePeriod doesn't have a value.

HIC

[annabergendal]

Thanks,

it was like you said, the variable had no value because it was never assigned.

Now that I solved that I have another problem.

Instead of date I want to use month as time, since the table should be on aggregated level (sum of quantities):

Join (Period_Qty_in_stock)

     Load Date((recno()+$(vMinDatePeriod)),'YYYY-MM') as MonthYear

     Autogenerate vMaxDatePeriod - vMinDatePeriod;

Qty_in_stock_per_period:

     NoConcatenate Load MonthYear,

          If( IsNull( Qty_in_stock_sum ), Peek( Qty_in_stock_sum ), Qty_in_stock_sum ) as Qty_in_stock_sum_per_period

          Resident Period_Qty_in_stock

          Order By MonthYear ;

But it doesn't work... In the join I get all dates instead of only the months... What should I do? Can I use Autogenerate and only get the months, not all dates?

[hic]

In principle, your method should work.

However, you must make sure that the MonthYear is defined the same way in both places. If you use a date serial number as month (~ 41000) then you can use

     Date( MonthStart( Date ), 'YYYY-MM' ) as MonthYear

But your Autogenerate creates one record per recno(), i.e. one record per date, so this must be changed to one record per month. One solution could be:

     Load distinct

          Date( MonthStart(recno()+$(vMinDatePeriod)),'YYYY-MM') as MonthYear

     Autogenerate vMaxDatePeriod - vMinDatePeriod;

Here the Autogenerate creates one record per month, but the distinct reduces it to one record per month. 

HIC

[annabergendal]

Thanks for the help, I realize I don't need Autogenerate though. I already have my TempTable (joining quantity table with periodcounter with months). But when I run

Qty_in_stock_per_period:

     NoConcatenate Load MonthYear,

          If( IsNull( Qty_in_stock_sum ), Peek(Qty_in_stock_sum), Qty_in_stock_sum ) as Qty_in_stock_sum_per_period,

          If( IsNull( Qty_in_stock_sum ), Previous(Qty_in_stock_sum), Qty_in_stock_sum ) as Qty_in_stock_sum_per_period_2

          Resident Period_Qty_in_stock

          Order By MonthYear;

          drop table Period_Qty_in_stock;

I still get null values for Qty_in_stock_sum_per_period, whilst in Qty_in_stock_sum_per_period_2 I get a value in the month with null after a month with value... Why can't the peek/previous find the values when null?

[hic]

Because Peek() looks in the output fields of the Load, whereas Previous() looks in the input fields of the Load. Peek(Qty_in_stock_sum) doesn't work since Qty_in_stock_sum doesn't belong to the output fields.

You need to use

     Qty_in_stock_per_period:

     NoConcatenate Load MonthYear,

          If( IsNull( InputQty ), Peek( OutputQty ), InputQty ) as OutputQty

          Resident Period_Qty_in_stock

          Order By MonthYear;

HIC