topic Issues with Cartesian product and populating missing dates in QlikView

Issues with Cartesian product and populating missing dates

Dayna — Wed, 03 Jul 2019 11:25:57 GMT

Good Afternoon!

I'm sure I'm doing something silly, but I'm having an issue with my load script for my cartesian product.

I have a set of records which have a quantity, i.e.

Product | Date | Quantity

ProdA | 03/07/2019 | 10

ProdA | 04/07/2019 | 20

ProdA | 08/07/2019 | 30

My script is below, but it's not populating the 'missing' dates that the product doesn't have a transaction for. I'd expect the script to create a record for 05/07/2019, 06/07/2019 and 07/07/2019 with the previous value.

Can someone point me in the direction to where I'm going wrong, please?

// ============= Find Min Date and Max Date MinMaxDate: Load Min(mrp_date) as MinDate, Max(mrp_date) as MaxDate Resident MRP; Let vMinDate = num(peek('MinDate', -1, 'MinMaxDate')) - 1; Let vMaxDate = num(peek('MaxDate', -1, 'MinMaxDate')); Drop Table MinMaxDate; // ============= Create cartesian product CartesianProduct: // 1) All dates Load Date(recno()+$(vMinDate)) as mrp_date Autogenerate vMaxDate - vMinDate; // 2) All products Left Join (CartesianProduct) Load distinct mrp_part resident MRP; CartesianProduct: LOAD mrp_part Resident MRP; join(CartesianProduct) Load Date(recno()+$(vMinDate)) as mrp_date Autogenerate vMaxDate - vMinDate; // ============= Join Cartesian product onto Data table Outer Join (MRP) Load * resident CartesianProduct; Drop Table CartesianProduct; // ============= 2nd pass through Data table - populate missing combinations temp: LOAD *, if(mrp_part=peek(mrp_part) and mrp_site = peek(mrp_site) and mrp_domain = peek(mrp_domain), RangeSum(mrp_qty, peek(mrp_cumul)),mrp_qty) as mrp_cumul, ROWNO() as mrp_row; LOAD mrp_weekyear, mrp_dataset, "mrp_detail", "mrp_domain", "mrp_nbr", mrp_date, mrp_line, mrp_part, mrp_site, monthname(mrp_date) as mrp_monthname, weekname(mrp_date) as mrp_weekname, year(mrp_date) as mrp_year, mrp_type, if(Len(Trim(mrp_qty))=0,0,mrp_qty) as mrp_qty RESIDENT MRP ORDER BY mrp_domain, mrp_site, mrp_part, mrp_weekyear, mrp_date ASC; Drop Table MRP;

Re: Issues with Cartesian product and populating missing dates

marcus_sommer — Wed, 03 Jul 2019 13:44:40 GMT

I must admit that I couldn't completely comprehend your example. The cartesian part seems to be doubled in your code. Personally I would probably concatenate (union in sql) the cartesian product to the fact-data, maybe in the following way:

MinMaxDate:
Load Min(mrp_date) as MinDate, Max(mrp_date) as MaxDate Resident MRP;
Let vMinDate = num(peek('MinDate', -1, 'MinMaxDate')) - 1;
Let vMaxDate = num(peek('MaxDate', -1, 'MinMaxDate'));
Drop Table MinMaxDate;

CartesianProduct:
Load Date(recno()+$(vMinDate)) as mrp_date Autogenerate vMaxDate - vMinDate;
Join (CartesianProduct) Load distinct mrp_part resident MRP;

concatenate(MRP) load *, 0 as Quantity, 'not exists' as Source resident CartesianProduct;
drop table CartesianProduct;

- Marcus

Re: Issues with Cartesian product and populating missing dates

Brett_Bleess — Tue, 30 Jul 2019 16:51:34 GMT

Have a look at the following Design Blog post, it is regarding setting up a master calendar, which I believe may do the trick here:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

There are some other calendar related posts in the Design Blog too, so if this one does not work, search on 'Calendar' and you should find the others to review.

Regards,
Brett