Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Recursive looping tables

I have been trying to run this in SQL but it isn't playing ball, so am now attempting in QV script - I need to split a total amount by the number of months that have been run.
Raw Data looks like this
IDnumber of monthsTotal Value
AAA113260
AAA215275
and I need to convert it to look like this for each ID
IDNumber of monthsCurrent MonthTotal ValueCurrent Value
AAA1131260Total Value/Number of Months
AAA1132260Total Value/Number of Months
AAA1133260Total Value/Number of Months
AAA1134260Total Value/Number of Months
AAA1135260Total Value/Number of Months
AAA1136260Total Value/Number of Months
AAA1137260Total Value/Number of Months
AAA1138260Total Value/Number of Months
AAA1139260Total Value/Number of Months
AAA11310260Total Value/Number of Months
AAA11311260Total Value/Number of Months
AAA11312260Total Value/Number of Months
AAA11313260Total Value/Number of Months
The number of months range from 3 up to 300.
Thanks in advance,
CJ
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Recursive looping tables

Like so:

Temp:

LOAD * INLINE [

    ID, NumOfMonths, Value

    AAA1, 13, 260

    AAA2, 15, 275

];

Data:

load *, IterNo() as CurrentMonth, Value/iterno() as CurrentValue Resident Temp while NumOfMonths > IterNo() -1;

drop table Temp;


talk is cheap, supply exceeds demand
3 Replies
MVP & Luminary
MVP & Luminary

Re: Recursive looping tables

Like so:

Temp:

LOAD * INLINE [

    ID, NumOfMonths, Value

    AAA1, 13, 260

    AAA2, 15, 275

];

Data:

load *, IterNo() as CurrentMonth, Value/iterno() as CurrentValue Resident Temp while NumOfMonths > IterNo() -1;

drop table Temp;


talk is cheap, supply exceeds demand
Not applicable

Re: Recursive looping tables

Excellent solution, very quick to run through as well - many thanks.

CJ

Re: Recursive looping tables

You may have to replace Value/iterno() by Value/NumOfMonths to get a fixed monthly value throughout a single year.

Peter