topic Re: fill previous values in App Development

fill previous values

harsha087 — Tue, 30 Nov 2021 16:25:32 GMT

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

Re: fill previous values

Saravanan_Desingh — Thu, 19 Aug 2021 20:00:57 GMT

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;

Re: fill previous values

Saravanan_Desingh — Thu, 19 Aug 2021 20:01:24 GMT

Output:

Re: fill previous values

Saravanan_Desingh — Fri, 20 Aug 2021 01:50:02 GMT

Missed the Order By in my previous code,

Re: fill previous values

Saravanan_Desingh — Fri, 20 Aug 2021 01:50:44 GMT

Output:

Re: fill previous values

harsha087 — Fri, 20 Aug 2021 04:41:30 GMT

Thank You .... 🙂