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

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

leivers1 — Sat, 16 Nov 2024 17:46:34 GMT

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

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

jwjackso — Wed, 25 Nov 2020 22:01:56 GMT

Check Henric's Generic Load

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

leivers1 — Thu, 26 Nov 2020 18:55:20 GMT

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