topic Iteration is not working for every item in QlikView

Iteration is not working for every item

TheQlikLearner — Tue, 31 Mar 2020 08:45:17 GMT

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;

Re: Iteration is not working for every item

MayilVahanan — Wed, 01 Apr 2020 10:22:17 GMT

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;

Re: Iteration is not working for every item

TheQlikLearner — Thu, 02 Apr 2020 07:21:15 GMT

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-date-values-for-a-given-id) and it is giving me result ,except that I have to get some help from expression to display exactly what I want.