topic Recursive looping tables in QlikView

Recursive looping tables

Thu, 29 Nov 2012 12:16:21 GMT

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

ID	number of months	Total Value
AAA1	13	260
AAA2	15	275

and I need to convert it to look like this for each ID

ID	Number of months	Current Month	Total Value	Current Value
AAA1	13	1	260	Total Value/Number of Months
AAA1	13	2	260	Total Value/Number of Months
AAA1	13	3	260	Total Value/Number of Months
AAA1	13	4	260	Total Value/Number of Months
AAA1	13	5	260	Total Value/Number of Months
AAA1	13	6	260	Total Value/Number of Months
AAA1	13	7	260	Total Value/Number of Months
AAA1	13	8	260	Total Value/Number of Months
AAA1	13	9	260	Total Value/Number of Months
AAA1	13	10	260	Total Value/Number of Months
AAA1	13	11	260	Total Value/Number of Months
AAA1	13	12	260	Total Value/Number of Months
AAA1	13	13	260	Total Value/Number of Months

The number of months range from 3 up to 300.

Thanks in advance,

Re: Recursive looping tables

Gysbert_Wassenaar — Thu, 29 Nov 2012 12:31:31 GMT

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;

Re: Recursive looping tables

Thu, 29 Nov 2012 12:46:47 GMT

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

Re: Recursive looping tables

Peter_Cammaert — Thu, 29 Nov 2012 12:47:24 GMT

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

Peter