Generate missing sales data for every month

[rhyseretto]

As the image above suggests, I'm looking to create records for every possible product/store/month combination - where that combination doesn't exist - and insert NULL for the Sales amount.

I've looked at this document; https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394 but can't work out how it would apply to my data - as far as I can tell, this only deals with filling in values using peek.

All help greatly appreciated, thanks.

[Vegar]

The following script will give provide the output presented above:

SET NULLINTERPRET='';
NULLASVALUE "*";
Set NullValue = 'NULL';
Data:
LOAD
  AutoNumberHash256(Product, Store, date#(Month, 'MMM')) as TMP_ID,
  Product,
  Store,
  date#(Month, 'MMM') as Month,
  Sales
inline [
  Product, Store, Month,Sales
  Apple,Toronto,Jan, 3
  Apple,Toronto,Feb, 6
  Orange,Toronto,Jan, 3
  Orange, Vancouver,Jan, 5
  Orange, Vancouver, Mar, 2
];

TMP:
NoConcatenate
LOAD Distinct Product Resident Data;
JOIN LOAD Distinct Store Resident Data;
Join LOAD Distinct Month, null() as Sales Resident Data;

Concatenate (Data)
LOAD
  AutoNumberHash256(Product, Store, Month) as TMP_ID,
  *
Resident TMP
WHERE
  exists(TMP_ID, AutoNumberHash256(Product, Store, Month) ) =0
;
DROP FIELD TMP_ID;
DROP TABLE TMP;

[rhyseretto]

@Vegar  thanks for the fast reply.

So as my data actually comes from a single qvd file, I assume I would replace the Inline section of your script with my QVD load? Is that correct?

Thanks.

[Vegar]

Yes, that is correct . Replace the inline [...] with FROM QVD_file.qvd (qvd)

[Or]

Note that if your dataset is large, it may be better to use a loop through the field values instead of using three individual Load DISTINCT statements. It tends to be significantly more efficient when the dataset is large and the list of distinct values is not too large.

e.g.

LOAD date(fieldvalue('Product',recno())) as Product
AUTOGENERATE fieldvaluecount('Product');

(Code snippet from: https://community.qlik.com/t5/QlikView-App-Dev/Alternative-to-load-distinct/td-p/306890 )

[rhyseretto]

@Or 

Good to know, thanks.