Iteration is not working for every item

[TheQlikLearner]

Hi all,

I am trying to display all the dates(in a range which I've defined) with 0 value if there is no data . But inside the script, the iteration seems not working for every item under product column. Currently only item 'Fruits' is displaying full range of date with respective sales. The rest 'Kitchen items', 'Vegetables','Clothes',..are not showing all the dates. Something is missing in my code. Can shed some light to make this working correctly?

I've attached my script too.

//iteration is working only for one product
SOURCE:
load * inline [
Product, Price, LogDate
Fruits, 100, 22/03/2020
Fruits, 90, 21/03/2020
Fruits, 150, 03/03/2020
Fruits, 160, 04/03/2020
Fruits, 170, 08/03/2020
Vegetable, 200, 22/03/2020
Vegetable, 200, 21/03/2020
'Kitchen Items', 200, 20/03/2020
Vegetable, 200, 19/03/2020
Clothes, 200, 21/03/2020
];

TempTable_Rates:
NoConcatenate Load Product, LogDate, Price Resident SOURCE ;

MinMaxDate:
Load Product, Min(LogDate) as MinDate, Max(LogDate) as MaxDate resident TempTable_Rates
Group By Product;

Join (TempTable_Rates)
Load
Product,
Date(iterno()+MinDate) as LogDate
Resident MinMaxDate
While iterno()+MinDate <= MaxDate;

Rates:
NoConcatenate Load Product, LogDate,
If( IsNull( Price ), 0, Price ) as Price
Resident TempTable_Rates
Order By Product, LogDate ;

Drop Table MinMaxDate, TempTable_Rates;

[MayilVahanan]

Hi

Try like below, hope it helps your requirement.

SOURCE:
load * inline [
Product, Price, LogDate
Fruits, 100, 22/03/2020
Fruits, 90, 21/03/2020
Fruits, 150, 03/03/2020
Fruits, 160, 04/03/2020
Fruits, 170, 08/03/2020
Vegetable, 200, 22/03/2020
Vegetable, 200, 21/03/2020
'Kitchen Items', 200, 20/03/2020
Vegetable, 200, 19/03/2020
Clothes, 200, 21/03/2020
];

TempTable_Rates:
NoConcatenate Load Product, LogDate, Price Resident SOURCE ;

MinMaxDate:
Load Product, Min(LogDate) as MinDate, Max(LogDate) as MaxDate resident TempTable_Rates
Group By Product;

//Join (TempTable_Rates)
Prod:
LOAD Distinct Product Resident SOURCE;
Join(Prod)
Load
Date(iterno()+MinDate) as LogDate
Resident MinMaxDate
While iterno()+MinDate <= MaxDate;

Join (TempTable_Rates)
LOAD * Resident Prod;

DROP Table Prod;

Rates:
NoConcatenate Load Product, LogDate,
If( IsNull( Price ), 0, Price ) as Price
Resident TempTable_Rates
Order By Product, LogDate ;

Drop Table MinMaxDate, TempTable_Rates, SOURCE;

[TheQlikLearner · Accepted solution]

Thank you for the help. Now it is looping for every product. But the original script is still incorrect . For the product(Fruit- the dates start from 3/03/2020) and the rest of the products(clothes, kitchen items,clothes,...starts from 04/03/2020) the dates are missing. That means Iteration still has an issue-min and max date are giving wrong result. However I've tried with another script(referenced from the post from https://stackoverflow.com/questions/49530748/qlikview-populating-missing-dates-and-peek-previous-dat...) and it is giving me result ,except that I have to get some help from expression to display exactly what I want.