Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rhyseretto
Contributor II
Contributor II

Generate missing sales data for every month

screen1.png

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.

Labels (3)
5 Replies
Vegar
Partner
Partner

Vegar_0-1614799994335.png

 

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;

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
rhyseretto
Contributor II
Contributor II
Author

@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
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Or
Master
Master

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
Contributor II
Contributor II
Author

@Or 

Good to know, thanks.