Fill missing date with previous value

[gnmq]

Hi, 

I'll like to achieve the output listed below.

TableA:

Product, Date, Amount

A, 12/13/2019, 500

A, 12/15/2019, 600

B, 12/13/2019, 227

Product, Date, Amount

A, 12/13/2019, 475

A, 12/14/2019, 475

A, 12/15/2019, 600

B, 12/13/2019, 227

B, 12/14/2019, 227

[Kushal_Chawda · Accepted solution]

may be this

TableA:
Load * Inline [
Product, Date, Amount
A, 12/13/2019, 500
A, 12/15/2019, 600
B, 12/13/2019, 227
B, 12/16/2019, 300 ];

Join(TableA)
Load Product,
     date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1<=MaxDate;
Load Product,
     date(max(Date)) as MaxDate,
     date(min(Date)) as MinDate
Resident TableA
Group by Product;

T2:
NoConcatenate
Load Product, 
     Date, 
     if(IsNull(Amount),Peek(Amount),Amount) as Amount
Resident TableA
Order By Product,Date;

Drop Table TableA;

if you have other fields in table and you want to fill the values then you need to include it using isnull. For eg. Description. You need to include it like below in above code

if(IsNull(Description),Peek(Description),Description) as Description