Populate Missing Data

[qlikviewaf]

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

[its_anandrjs]

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

[qlikviewaf]

it's the row generated by the script

[qlikviewaf]

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?

[Kushal_Chawda]

may be this will be helpful

Generating Missing Data In QlikView

[qlikviewaf]

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)

[Kushal_Chawda]

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

[Kushal_Chawda]

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;

[qlikviewaf]

tried both, not working

[Kushal_Chawda]

are you getting any error ?