Generate missing data

[Mohan23912391]

Hi, I have data like this

Period	Market	ID	Amount
202107	ES	1	500
202108	ES	1	400
202110	ES	1	200
202111	ES	1	100
202201	ES	1	50

and I want my result like this

Period	Market	ID	Amount
202107	ES	1	500
202108	ES	1	400
202109	ES	1	400
202110	ES	1	200
202111	ES	1	100
202112	ES	1	100
202201	ES	1	50

basically, I need to generate the missing records(202109, 202112 are missing) as well with Amount as previous amount.

please help in script.

[vinieme12]

temp:
load
Period,Market,ID,Amount
From Xyzsource;

minmax:
Load 
monthstart(date#(Min(Period),'YYYYMM')) as mindate
monthstart(date#(Max(Period),'YYYYMM')) as maxdate
Resident;

let vmindate = peek('mindate',0,'minmax');
let vmaxdate = peek('maxdate',0,'minmax');

LEft Join(temp)
Load 
Date(monthstart($(vmindate),iterno()-1),'YYYYMM') as Period
Autogenerate(1)
While monthstart($(vmindate),iterno()-1)<=$(vmindate);


NOCONCATENATE
Main:
load
Period,
if(len(Market),Market,Peek('Market')) as Market
if(len(ID),Market,Peek('ID')) as ID
if(len(Amount),Amount,Peek('Amount')) as Amount
Resident temp Order by Period;

drop table temp;

[Mohan23912391]

Hi @vinieme12 , Thanks for your quick reply.

I have used your script but no new records got created in middle.

I have even tried by changing

- mindate to maxdate(highlighted in Red font below)

- left join to JOIN(highlighted in Red font below)

still, no luck.

LEft Join(temp)                   /*-------JOIN------*/
Load
Date(monthstart($(vmindate),iterno()-1),'YYYYMM') as Period
Autogenerate(1)
While monthstart($(vmindate),iterno()-1)<=$(vmaxdate);           /*------------*/

can you suggest any further changes needed?