Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!

Generating Missing Data In QlikView

cancel
Showing results for 
Search instead for 
Did you mean: 
hic
Former Employee
Former Employee

Generating Missing Data In QlikView

Last Update:

Feb 4, 2013 8:35:25 AM

Updated By:

hic

Created date:

Feb 4, 2013 8:35:25 AM

Attachments
  • What techniques are there to generate “new” tables; tables that don’t exist in the source data?
  • How do I loop over one and the same record in source data?
  • How do I propagate a value from above record to the current one?
  • How do I populate a sparsely populated field?
  • How do I generate all combinations – the Cartesian product – of two or more fields?
  • How do I make simulations in QlikView?

These questions and others are answered in this Technical Brief.

HIC

Comments
dsharmaqv
Creator III
Creator III

good one

Anonymous
Not applicable

Thanks Henric and Ellen. I was trying to use this approach and dont know whats going wrong here

If i dont use flags and use the calculations directly in the expression tab it is working fine but when i use it in set analysis with flag i am getting double the count. But when i click on the on a date in Referencedateweek it shows correct details. I am not able to understand why this is happening?

My dimension is ReferencedateMonth and my expression is count(distinct {<vflag_non_null_Resolve_dates_monthly={'1'}>}[Problem Number])
+
count(distinct{<vflag_null_Resolve_dates_monthly={'1'}>}[Problem Number] )

Below is my script

//Generation of missing dates for Problems to track outstanding tickets
Open_Referencedates:
load
[Problem Number] ,
date(Date_Open_date_PR + IterNo() - 1) as Referencedate,
monthname(Date_Open_date_PR + IterNo() - 1,0) as ReferencedateMonth,
Date_Open_date_PR as Opened_PR,
Date_Resolve_date_PR as Resolved_PR

Resident Resident_TR_Dump

while IterNo() < today() - Date_Open_date_PR +1;


load
[Problem Number] ,

if(year(Referencedate)>'2013',Weekend(Referencedate)) as Referencedateweek,

//if(monthstart(Referencedate)>=AddMonths(MonthStart(today()),-13) and monthstart(Referencedate)<AddMonths(MonthStart(today()),0) ,monthname(Referencedate)) as Last13Months_Problems,

if( IsNull(Resolved_PR)=-1 and WeekStart(Opened_PR)<=WeekStart(Referencedate),1,0 ) as vflag_null_Resolve_dates,

if( WeekStart(Resolved_PR) > WeekStart(Referencedate) and WeekStart(Opened_PR)<=WeekStart(Referencedate),1,0) as vflag_non_null_Resolve_dates,

if(IsNull(Resolved_PR)=-1 and Monthname(Opened_PR)<=Monthname(Referencedate),1,0) as vflag_null_Resolve_dates_monthly,

if(MonthStart(Resolved_PR) > MonthStart(Referencedate) and MonthStart(Opened_PR,0)<=MonthStart(Referencedate),1,0) as vflag_non_null_Resolve_dates_monthly

resident Open_Referencedates;

I have used the same expression for Referencedateweek and it worked fine but when i try to convert it into months it starts showing wrong results

Anonymous
Not applicable

Thanks ellen.. It took me a while to figure this one out but your solution finally worked

Anonymous
Not applicable

Thanks Henric.. I rummaged through some of the blogs and your solution ..i redid the whole script and it worked fine with your solution and ellen's suggestions though it did take good deal of time but it was worth the effort

Not applicable

Hi Henric,

I am trying to generate the missing products for each client. For instance, one client would only purchased from Product1 so it does not have any record from the other products. See below the sample table;

AccountIdProduct LineRun Rate
0017000000b6jLqProduct1$59
Product2-
Product3-
Product4-
Product5-

The goal is to see the product gaps so the only way is to load all missing values for all clients.

Below my script;

Data1:

Load

  1 as AddData,

     %Date,

     AccountId,

     [Top Level Parent Name],

     AccountName,

     [Product Line],

     Product_Category__c,

     Product_Sub_Category__c,

     Budget_Sub_Type__c,

     Budget_Type__c,

     Region,

     Country_rev,

     [Finance Segment],

     Run_Rate__c

  resident

  Sales

  where Record_Type__c = 'Run Rate'

  ;

CartesianProduct:

Load

  Distinct

  AccountId

  Resident

  Data1;

Left Join

Load

  Distinct

  [Product Line]

  Resident

  Data1;

Outer join (Data1)

Load

*

resident

CartesianProduct; 

Drop Table CartesianProduct;

AddData2:

Concatenate(Sales)

Load

  AccountId,

  [Product Line],

  if(isnull(Run_Rate__c),0,Run_Rate__c) as Run_Rate__c

  Resident

  Data1;

Drop table Data1;

I cannot get to add the remaining products (e.g. from Product2 - to Product5) where data does not exist.

I am trying to follow you methodology but without success. Could you point what I am doing wrong?

hic
Former Employee
Former Employee

Change your "Left Join" to "Join" (i.e. an outer join) and it will probably work.

HIC

Not applicable

It did not work

Data1:

Load

  1 as AddData,

     %Date,

     AccountId,

     [Top Level Parent Name],

     AccountName,

     [Product Line],

     Product_Category__c,

     Product_Sub_Category__c,

     Budget_Sub_Type__c,

     Budget_Type__c,

     Region,

     Country_rev,

     [Finance Segment],

     Run_Rate__c

  resident

  Sales

  where Record_Type__c = 'Run Rate'

  ;

CartesianProduct:

Load

  Distinct

  AccountId

  Resident

  Data1;

Outer Join (CartesianProduct)

Load

  Distinct

  [Product Line]

  Resident

  Data1;

Outer join (Data1)

Load

*

resident

CartesianProduct; 

Drop Table CartesianProduct;

AddData2:

Concatenate(Sales)

Load

  AccountId,

  [Product Line],

  if(isnull(Run_Rate__c),0,Run_Rate__c) as Run_Rate__c

  Resident

  Data1;

Drop table Data1;

hic
Former Employee
Former Employee

You need to include also a date or a month in the Cartesian product. Try something like the following:

Sales:

Load

     AccountId & '|' & [Product Line] & '|' & Num(MonthStart(%Date)) as Key,

     ...

resident Sales

  where Record_Type__c = 'Run Rate';

CartesianProduct:

Load Distinct AccountId Resident Sales;

Join (CartesianProduct)

Load Distinct [Product Line] Resident Sales;

Join (CartesianProduct)

Load Distinct Num(MonthStart(%Date)) as MonthStart Resident Sales;

Concatenate(Sales)

Load

     AccountId & '|' & [Product Line] & '|' & MonthStart as Key,

     AccountId,

     [Product Line],

     MonthStart as %Date,

     0 as Run_Rate__c

  Resident CartesianProduct

     Where not exists(Key,AccountId & '|' & [Product Line] & '|' & MonthStart);

Drop table CartesianProduct;

Not applicable

It worked, many thanks Henric for the prompt and accurate resolution

Cheers

crossroadsit
Contributor II
Contributor II

Wonderful material. Not surprised that it's still an active thread!

I think my case is similar to that addressed on page 12 of the PDF but I have some differences to deal with and I'm having trouble getting it right.

I have a single "log" table that shows the daily rate each user is being charged as of a certain date.

  • An entry is only logged when the users' rates change.
  • A single user could have dozens of entries in this table with arbitrary gaps of time in between.

I would like to fill in the gaps so that every user has row for every date from their first entry in the log table until either

  • If their last entry in the log has a rate of 0 then that's the last date we want to fill in entries up until.
  • If their last entry in the log rate a rate >0 then we want to fill in dates up until today
  • So in the data below: user3 would have entries from 14/01/2016 until today but user4 would have entries from 26/01/2016 until 27/04/2016 only.

Obviously the rates filled in for each user should be a fill-down from their last entry in the log.

I'm not sure how to manipulate the vMinDate and vMaxDate it the attached examples to handle this. Sorry if it should be obvious but I'm breaking my brain and would love a tip

  

userIDdatedailyrate
user19/01/201625
user211/01/201650
user314/01/201615
user426/01/201620
user412/02/201630
user318/02/20160
user227/02/201688
user119/03/201620
user125/03/201623
user230/03/201689
user32/04/201615
user14/04/201640
user46/04/201630
user427/04/20160
user36/05/201623
user211/05/201632
Version history
Last update:
‎2013-02-04 08:35 AM
Updated by:
Former Employee