How to create a table based on data from an existing table in script part 2

[Anonymous]

I have a similar but for me more challenging question on how to create a table based on some field.

This is what I need:

There is an record in an existing table with ID=A, start Date=May, Months=36 and Value =360.

Based on this I would like to create a new table which splits the value into 12 months lines. The close date for each new line needs to be a date in the month preceding the start month. In the example below I took the 25th of the month before.

The line for the first 12 months I already have calculated **, so ideally there is no need anymore to create the Italic lines in grey you see in the "To Be Created" table. But if it's easier, then I'm fine if they are created as I guess I am able to remove the duplicates in some way.

I hope my challenge is clear ?

** if months > 12 then Value = 12 * (value/months). & Close date is in reality also a field on the existing table

[Anonymous · Accepted solution]

I found the answer, with help of the previous answer I got and some logical thinking

TO_BE_CREATED:

LOAD

"ID"&'-'&(IterNo()+1),

    AddYears("Start Date",IterNo())    as "TB Start Date",

     if(("Months"-12*IterNo())>12,12,"Months-12*IterNo())    as "TB Months",

    if(("Months"-12*IterNo())>12,12*"Value"/"Months",("Months"-12*IterNo())*"Value"/"Months") as "TB Value"

Resident Existing_Table

While "Months"-12*(IterNo()-1)>12;