Get Previous Date Value if No Value in next date

[Shahzad_Ahsan]

Hi 

I have a table like this

T1:

Category	Date	Value
A	03/12/2019	968
B	03/12/2019	478
C	03/12/2019	259
D	03/12/2019	478
A	04/12/2019	300
B	04/12/2019	658
C	04/12/2019	478
D	04/12/2019	214
A	05/12/2019	200
D	05/12/2019	800

I need result like this

T2:

Category	Date	Value
A	03/12/2019	968
B	03/12/2019	478
C	03/12/2019	259
D	03/12/2019	478
A	04/12/2019	300
B	04/12/2019	658
C	04/12/2019	478
D	04/12/2019	214
A	05/12/2019	200
B	05/12/2019	658
C	05/12/2019	478
D	05/12/2019	800

The difference is, if you look at T1 there is missing value for Category B and C for 05/12/2019 

If want to insert Category B and C for 05/12/2019 with the same value of B and C from 04/12/2019

I need 4 rows for each date from first date till today. 

Please suggest how to achieve this from load script.

[lironbaram · Accepted solution]

hi 

this script is an example , that gives you what you looked for 

T1temp:
LOAD * INLINE 
[
Category,Date,Value
A,03/12/2019,968
B,03/12/2019,478
C,03/12/2019,259
D,03/12/2019,478
A,04/12/2019,300
B,04/12/2019,658
C,04/12/2019,478
D,04/12/2019,214
A,05/12/2019,200
D,05/12/2019,800
](delimiter is ',');

///////////creating a table with all dates and all category //////////////////////
T2temp:
load Distinct Date
Resident T1temp;
Join
load Distinct Category
Resident T1temp;
/////////////adding the actual data to the full table //////////////////////////////
left join (T2temp)
load *
Resident T1temp;

drop Table T1temp;
////////////////filling the missing values //////////////////
T2:
load *,
if(len(Value)<1,if(Previous(Category)=Category, peek('fullValue'),0),Value) as fullValue
Resident T2temp
Order by Category,Date;

drop table T2temp;

[Shahzad_Ahsan]

Thanks Buddy

This is what I needed