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

Anonymous — Fri, 21 Jul 2017 06:24:57 GMT

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

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

Anonymous — Mon, 24 Jul 2017 06:25:47 GMT

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;

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

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

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