topic Re: Populate Missing Data in QlikView

Populate Missing Data

qlikviewaf — Wed, 13 Sep 2017 09:08:01 GMT

Hi,

in my script i have:

Area
Region
Invoice Date

i would like to generate all missing invoice date records on the sales table for Region/Area combination.

e.g.

Sales1 - 10/09/2017 - Area1 - Region1

Sales2 - 13/09/2017 - Area1 - Region2

I expect to have generated the missing combination::

SalesGenerated1 - 10/09/2017 - Area1 - Region2

SalesGenerated2 - 13/09/2017 - Area1 - Region1

Anyone can help with this?

Thank you

Re: Populate Missing Data

its_anandrjs — Wed, 13 Sep 2017 09:12:53 GMT

Can you please explain this row SalesGenerated1 - 10/09/2017 - Area1 - Region2 more clearly i not understand how it will Region2

Re: Populate Missing Data

qlikviewaf — Wed, 13 Sep 2017 09:14:44 GMT

it's the row generated by the script

Re: Populate Missing Data

qlikviewaf — Wed, 13 Sep 2017 09:16:45 GMT

I expect that the script looks the minimum and maxium date for the data set, and then generate record for each day missing for all combination of Area and Region. Make sense?

Re: Populate Missing Data

Kushal_Chawda — Wed, 13 Sep 2017 10:24:08 GMT

may be this will be helpful

Generating Missing Data In QlikView

Re: Populate Missing Data

qlikviewaf — Wed, 13 Sep 2017 10:35:22 GMT

Based on that i wrote this script, but i dont get why i get the error below, it's exactly the same script as in the document.

Can you kindly help?

---------------------------------------------------------------------------------

COMBINATION_EXTRACT:

LOAD Area,Region,Region_Description,Invoice_Date_Num,Product_Line,

Area&Region&Region_Description&Product_Line&'|'&Num(Invoice_Date_Num) as MissingData

RESIDENT F42119;

COMBINATION_GENERATION:

LOAD DISTINCT Area,Region,Region_Description,Product_Line

RESIDENT COMBINATION_EXTRACT;

JOIN (COMBINATION_GENERATION)

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

CONCATENATE(F42119)

LOAD * Where not exists (MissingData);

LOAD Area,Region,Region_Description,Invoice_Date_Num,Product_Line,

Area&Region&Region_Description&Product_Line&'|'&Num(Invoice_Date_Num)

RESIDENT COMBINATION_GENERATION;

---------------------------

Error in expression:

')' expected

JOIN (COMBINATION_GENERATION)

LOAD Date(recno()+) as Invoice_Date_Num Autogenerate(vMaxDate - vMinDate)

Re: Populate Missing Data

Kushal_Chawda — Wed, 13 Sep 2017 10:39:59 GMT

you can try below

COMBINATION_EXTRACT:

LOAD Area,Region,Region_Description,Invoice_Date_Num,Product_Line,

Area&Region&Region_Description&Product_Line&'|'&Num(Invoice_Date_Num) as MissingData

RESIDENT F42119;

join(COMBINATION_EXTRACT)

LOAD date(MinDate+IterNo()-1) as DateAll

While MinDate+IterNo()-1<=MaxDate;

LOAD min(Date) as MinDate,

max(Date) as MaxDate;

LOAD FieldValue('Invoice_Date_Num',RecNo()) as Date

AutoGenerate FieldValueCount('Invoice_Date_Num');

Further you can build the Caledar on DateAll

Re: Populate Missing Data

Kushal_Chawda — Wed, 13 Sep 2017 10:43:06 GMT

or with your approach

COMBINATION_EXTRACT:

LOAD Area,Region,Region_Description,Invoice_Date_Num,Product_Line,

Area&Region&Region_Description&Product_Line&'|'&Num(Invoice_Date_Num) as MissingData

RESIDENT F42119;

COMBINATION_GENERATION:

LOAD DISTINCT Area,Region,Region_Description,Product_Line

RESIDENT COMBINATION_EXTRACT;

JOIN (COMBINATION_GENERATION)

LOAD date(MinDate+IterNo()-1) as Invoice_Date_Num

While MinDate+IterNo()-1<=MaxDate;

LOAD min(Date) as MinDate,

max(Date) as MaxDate;

LOAD FieldValue('Invoice_Date_Num',RecNo()) as Date

AutoGenerate FieldValueCount('Invoice_Date_Num');;

CONCATENATE(F42119)

LOAD * Where not exists (MissingData);

LOAD Area,Region,Region_Description,Invoice_Date_Num,Product_Line,

Area&Region&Region_Description&Product_Line&'|'&Num(Invoice_Date_Num)

RESIDENT COMBINATION_GENERATION;

Re: Populate Missing Data

qlikviewaf — Wed, 13 Sep 2017 10:55:22 GMT

tried both, not working

Re: Populate Missing Data

Kushal_Chawda — Wed, 13 Sep 2017 11:15:09 GMT

are you getting any error ?

Re: Populate Missing Data

Kushal_Chawda — Wed, 13 Sep 2017 11:15:58 GMT

It will be helpful if you can share the sample