If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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.
The following script will give provide the output presented above:
Set NullValue = 'NULL';
AutoNumberHash256(Product, Store, date#(Month, 'MMM')) as TMP_ID,
date#(Month, 'MMM') as Month,
Product, Store, Month,Sales
Orange, Vancouver,Jan, 5
Orange, Vancouver, Mar, 2
LOAD Distinct Product Resident Data;
JOIN LOAD Distinct Store Resident Data;
Join LOAD Distinct Month, null() as Sales Resident Data;
AutoNumberHash256(Product, Store, Month) as TMP_ID,
exists(TMP_ID, AutoNumberHash256(Product, Store, Month) ) =0
DROP FIELD TMP_ID;
DROP TABLE TMP;
@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?
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.
LOAD date(fieldvalue('Product',recno())) as Product
(Code snippet from: https://community.qlik.com/t5/QlikView-App-Dev/Alternative-to-load-distinct/td-p/306890 )