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

[Anonymous]

Hi,

Is it possible to create the right table based on the existing table on the left in a script ?

My guess is that it can be done with For..Next.

This is what I need:

There is an record in an existing table with ID=A, start Date=June, Units=4 and Value =100.

Based on this I would like to create a new table which has 4 (=Units) lines, each with an amount of Value/Units and a date which starts at the start date and increases with 1 month

 

[tomasz_tru]

Is there an upper limit for the unit field?

Tomasz

[antoniotiman]

Hi Eric,

LOAD ID,AddMonths(Date#(StartDate,'1-MMM'),IterNo()-1) as StartDate,
Value/Units as Amount
Inline [
ID,StartDate,Units,Value
A,1-jun,4,100
B,1-feb,2,200
C,1-jul,3,90]
While IterNo() <= Units;

Regards,

Antonio

[Anonymous]

Not perse, but I don't expect it to be more than 36 (months)

[Anonymous]

Hi Antonio,

Not sure if I understand this fully. You put in the values of the existing table within the Inline statement ( A,1-jun,4,100
B,1-feb,2,200 and  C,1-jul,3,90 ).

I need to replace this part of script by a reference to the Existing table as It need to read these lines from the table, right ?

Imagine the existing table name is : OPPS, how should the script look like ?

[antoniotiman · Accepted solution]

LOAD ID,AddMonths(Date#(StartDate,'1-MMM'),IterNo()-1) as StartDate, Value/Units as Amount

Resident OPPS      // if already loaded or  From Path\Table.xls or From DB_Table

While IterNo() <= Units;