Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Output:
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;
Output:
Thank You .... 🙂