Recursive looping tables

[#1]

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,

CJ

[Gysbert_Wassenaar · Accepted solution]

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;

[#3]

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

CJ

[Peter_Cammaert]

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

Peter