topic Re: Fill missing date with previous available's day value in App Development

Fill missing date with previous value

gnmq — Mon, 20 Dec 2021 21:08:46 GMT

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

Re: Fill missing date with previous available's day value

Kushal_Chawda — Fri, 21 Aug 2020 09:31:25 GMT

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