How to create fields using for loop with load statement

[mohitsharmaalexion]

Hi,

I am trying to generate fields within a table for lag periods, but not sure what I am doing wrong here. What I want is to create a lag column with value of sales equal to previous period. Now here variable vMax_period is basically the maximum number assigned to the period. if at present I have 50 period, I want this table to have 49 lag columns.

My try:

for j=1 to $(vMax_Period)
     [SALES_LAGS]:
     Load
         KEY,
         PERIOD,
         If(KEY = PEEK('KEY',-$(j)),Peek('TOTAL_ORDER_QTY',-$(j))) as [lag_$(j)]
     resident SALES
     order by by KEY,PERIOD;
next j;

[micheledenardi]

Just remove the square brackets [] from the field name:

[Periods]:
Load
   null() as Period_0
AutoGenerate(0);


for j=1 to 12
     Concatenate(Periods)
     Load
         $(j) as Period_$(j)
     AutoGenerate(1);
next j;
Drop Field Period_0;

[mohitsharmaalexion · Accepted solution]

Found the issue with my problem. Basically I need to keep field TOTAL_ORDER_QTY in load statement otherwise I'll always get NULL. So correct script should be:

for j=1 to $(vMax_Period)
     [SALES_LAGS]:
     Load
         KEY,
         PERIOD,
         TOTAL_ORDER_QTY,
         If(KEY = PEEK('KEY',-$(j)),Peek('TOTAL_ORDER_QTY',-$(j))) as [lag_$(j)]
     resident SALES
     order by by KEY,PERIOD;
next j;