Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
We value your feedback – take our 5-minute QlikView modernization survey
cancel
Showing results for 
Search instead for 
Did you mean: 
TheQlikLearner
Partner - Contributor
Partner - Contributor

Iteration is not working for every item

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;

 

1 Solution

Accepted Solutions
TheQlikLearner
Partner - Contributor
Partner - Contributor
Author

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.

View solution in original post

2 Replies
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;

Thanks & Regards,
Mayil Vahanan R
TheQlikLearner
Partner - Contributor
Partner - Contributor
Author

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.