fill previous values

[harsha087]

Hi All,

Below is my input table 

Purchase_Date	Car_Brand	Price	Cars Sold Till Date
01-01-2021	BMW	9581304	1
02-01-2021	BMW	2664167	2
03-01-2021	BMW	6034667	3
04-01-2021	BMW	1869793	4
05-01-2021	BMW	2167784	5
10-01-2021	Mercedes	9478128	6
13-01-2021	Mercedes	8705679	7
14-01-2021	Mercedes	8012033	8
15-01-2021	Jaguar	4448233	9
16-01-2021	Jaguar	2316127	10
21-01-2021	Jaguar	3185625	11

expected output

i was able to create missing date ... 

can anyone help to get the previous value for the missing date records

[Saravanan_Desingh]

Try this,

SET DateFormat='DD-MM-YYYY';

tab1:
LOAD * INLINE [
    Purchase_Date, Car_Brand, Price, Cars Sold Till Date
    01-01-2021, BMW, 9581304, 1
    02-01-2021, BMW, 2664167, 2
    03-01-2021, BMW, 6034667, 3
    04-01-2021, BMW, 1869793, 4
    05-01-2021, BMW, 2167784, 5
    10-01-2021, Mercedes, 9478128, 6
    13-01-2021, Mercedes, 8705679, 7
    14-01-2021, Mercedes, 8012033, 8
    15-01-2021, Jaguar, 4448233, 9
    16-01-2021, Jaguar, 2316127, 10
    21-01-2021, Jaguar, 3185625, 11
];

Concatenate(tab1)
LOAD PDt As Purchase_Date
Where Not Exists(Purchase_Date,PDt);
LOAD Date(MinDt+IterNo()) As PDt
While MaxDt >= MinDt+IterNo();
LOAD Min(Purchase_Date) As MinDt, Max(Purchase_Date) As MaxDt
Resident tab1;

tab2:
NoConcatenate
LOAD Purchase_Date, 
	If(IsNull(Car_Brand),Peek(Car_Brand),Car_Brand) As Car_Brand, 
	If(IsNull(Price),Peek(Price),Price) As Price, 
	If(IsNull([Cars Sold Till Date]),Peek([Cars Sold Till Date]),[Cars Sold Till Date]) As [Cars Sold Till Date]
Resident tab1;

Drop Table tab1;

[Saravanan_Desingh]

Output:

[Saravanan_Desingh · Accepted solution]

Missed the Order By in my previous code,

SET DateFormat='DD-MM-YYYY';

tab1:
LOAD * INLINE [
    Purchase_Date, Car_Brand, Price, Cars Sold Till Date
    01-01-2021, BMW, 9581304, 1
    02-01-2021, BMW, 2664167, 2
    03-01-2021, BMW, 6034667, 3
    04-01-2021, BMW, 1869793, 4
    05-01-2021, BMW, 2167784, 5
    10-01-2021, Mercedes, 9478128, 6
    13-01-2021, Mercedes, 8705679, 7
    14-01-2021, Mercedes, 8012033, 8
    15-01-2021, Jaguar, 4448233, 9
    16-01-2021, Jaguar, 2316127, 10
    21-01-2021, Jaguar, 3185625, 11
];

Concatenate(tab1)
LOAD PDt As Purchase_Date
Where Not Exists(Purchase_Date,PDt);
LOAD Date(MinDt+IterNo()) As PDt
While MaxDt >= MinDt+IterNo();
LOAD Min(Purchase_Date) As MinDt, Max(Purchase_Date) As MaxDt
Resident tab1;

tab2:
NoConcatenate
LOAD Purchase_Date, 
	If(IsNull(Car_Brand),Peek(Car_Brand),Car_Brand) As Car_Brand, 
	If(IsNull(Price),Peek(Price),Price) As Price, 
	If(IsNull([Cars Sold Till Date]),Peek([Cars Sold Till Date]),[Cars Sold Till Date]) As [Cars Sold Till Date]
Resident tab1
Order By Purchase_Date;

Drop Table tab1;

[Saravanan_Desingh]

Output:

[harsha087]

Thank You ....  🙂