Generating Missing Data In QlikView

hic · Feb 4, 2013 8:35:25 AM

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.

dsharmaqv · ‎2017-01-10

good one

Anonymous · ‎2017-01-11

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 · ‎2017-01-23

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

Anonymous · ‎2017-01-23

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

Move Comment · ‎2017-02-07

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;

AccountId	Product Line	Run Rate
0017000000b6jLq	Product1	$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 · ‎2017-02-08

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

HIC

Move Comment · ‎2017-02-08

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 · ‎2017-02-09

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;

Move Comment · ‎2017-02-09

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

Cheers

crossroadsit · ‎2017-03-07

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

userID	date	dailyrate
user1	9/01/2016	25
user2	11/01/2016	50
user3	14/01/2016	15
user4	26/01/2016	20
user4	12/02/2016	30
user3	18/02/2016	0
user2	27/02/2016	88
user1	19/03/2016	20
user1	25/03/2016	23
user2	30/03/2016	89
user3	2/04/2016	15
user1	4/04/2016	40
user4	6/04/2016	30
user4	27/04/2016	0
user3	6/05/2016	23
user2	11/05/2016	32

Generating Missing Data In QlikView

Generating Missing Data In QlikView

App Development

Scripting

Technical Brief