Create table in load and fill columns with values from existing tables

[leivers1]

Hi,

I am struggling how to do this. I have a Forecast Table with  

Forecast Cycle (2020-M03)

Year Month (A forecast Cycle will forecast for 24months in future)

Customer Group

Product

Forecast

I have a sales table with 

Year Month

Customer Group

Product

Sales

I want to create a final table comparing  the Sales minus Forecast For each Year month Product Customer group combination.

Month Year

Product id

Customer Group

Sales

Forecast-1

Forecast-2 etc

In Final Table i want 12 columns (Forecast -x) and in those i want to populate;

For the current row where Month Year = 2020-01 I want the column Forecast-1 so be populate with the Sum of the value forecasted in the cycle of Month year -1 for that MonthYear and its Product/Group combination. and so on for the Forecast Cycle of Month year -2.

i.e if looking at 2020-03 year month; in Forecast -1 i expect to see the value of that was forecast in 2020-02 version but for the month 2020-03 

I am really struggling here; i have tried to pivot the Forecast data so that it is also on month year level.

I am thinking a for loop is needed here but i am very weak in this area.

Thanks,

Lara

[jwjackso · Accepted solution]

Check Henric's Generic Load 

[leivers1]

Thanks so much for your quick reply. i think  now have my data in the correct format (below) where i have my sales and the forecast values together by year month.

But how can i create and populate new columns Forecast -1,-2 etc. using this new table.

I want; that if the row's Year month value is 2020-03; then i want to populate the column "Forecast-1" with the value in the column "2020-02" for that row and repeat this for the row; up until Forecast -12.

THanks,

Laura