Issues with Cartesian product and populating missing dates

[Dayna]

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;

[marcus_sommer]

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

[Brett_Bleess]

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