Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
harsha087
Contributor III
Contributor III

fill previous values

Hi All,

 

Below is my input table 

Purchase_DateCar_BrandPriceCars Sold Till Date
01-01-2021BMW95813041
02-01-2021BMW26641672
03-01-2021BMW60346673
04-01-2021BMW18697934
05-01-2021BMW21677845
10-01-2021Mercedes94781286
13-01-2021Mercedes87056797
14-01-2021Mercedes80120338
15-01-2021Jaguar44482339
16-01-2021Jaguar231612710
21-01-2021Jaguar318562511

 

 

expected output

harsha087_0-1629392495828.png

 

i was able to create missing date ... 

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

 

 

Labels (1)
1 Solution

Accepted Solutions
Saravanan_Desingh

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;

View solution in original post

5 Replies
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:

commQV85.PNG

Saravanan_Desingh

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:

commQV86.PNG

harsha087
Contributor III
Contributor III
Author

Thank You ....  🙂