Create new table using double loop from resident

[maryna_hubska]

Hello! I have one table

Now I need to create another, where for each id_plan I will have endDate-startDate in days rows with the same cats_id and the number of plans calculated as plan/interval#(endDate-startDate,'DD').

I want to do this in load editor, but do not know how to write a code

[RafaelBarrios]

Hi @maryna_hubska 

you dont need to load another table for that, you can do it while loading the table or with a precedent load

normal_loading (calculate new field while loading)

Load
   plan/(floor(endDate-startDate)) as calculated_field,
   id_plan,
   cats_id,
   plan,
   startDate,
   endDate
from where_ever_you_need;

precendent (with an inline example)

Load
   floor(endDate-startDate) as interv,
   plan/(floor(endDate-startDate)) as calculated_field,
   *;
load * inline [
id_plan,cats_id,plan,startDate,endDate
1,1,40000,31.12.2021 10:00:00 PM,31.12.2022 9:59:59 PM
2,2,100,30.06.2021 9:00:00 PM,31.08.2021 8:59:59 PM
3,2,300,31.07.2021 9:00:00 PM,08.08.2021 8:59:59 PM
];

i use floor to take out the decimal from the time difference 

but, if you still need to do it in a different table you can try RESIDENT load and your new table will be connected to previous by id_plan

new_table_name:
Load
    plan/(floor(endDate-startDate)) as calculated_field,
    id_plan,
RESIDENT <your_previous_loadded_table_name>;

hope it helps

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button!