Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

aditya_k_d
New Contributor II

Peek and Previous Function to Fill Missing Values

Hi All,

I have sample data as shown below

Reporter IDTransaction IDTransaction DateSales
R1T11-Jan-19100
R1T15-Jan-1980
R1T110-Jan-1960
R1T115-Jan-1940

 

The requirement is to fill the intermediate missing dates with previous sales values as something like shown below

Reporter IDTransaction IDTransaction DateSales
R1T11-Jan-19100
R1T12-Jan-19100
R1T13-Jan-19100
R1T14-Jan-19100
R1T15-Jan-1980
R1T16-Jan-1980
R1T17-Jan-1980
R1T18-Jan-1980
R1T19-Jan-1980
R1T110-Jan-1960
R1T111-Jan-1960
R1T112-Jan-1960
R1T113-Jan-1960
R1T114-Jan-1960
R1T115-Jan-1940

 

I was trying to use peek and previous condition but not able to replicate it exactly.

 

Any help is appreciated.

 

Thanks

Labels (3)
1 Reply
mrybalko
Contributor II

Re: Peek and Previous Function to Fill Missing Values

Hello @aditya_k_d 

 

tmp:
LOAD
	[Reporter ID],
	[Transaction ID],
	Sales,
	Date(Date#([Transaction Date], 'DD-MMM-YY')) as 'Start date'
Inline [
Reporter ID, Transaction ID, Transaction Date, Sales
R1, T1, 1-Jan-19, 100
R1, T1, 5-Jan-19, 80
R1, T1, 10-Jan-19, 60
R1, T1, 15-Jan-19, 40
];

Periods:
LOAD
	*,
	alt(Peek([Start date]), [Start date]) as 'End date'
Resident tmp
Order by 	[Reporter ID], 
			[Transaction ID],
			[Start date] desc;

drop Table tmp;

Data:
LOAD
	*,
	Date([Start date] + IterNo() - 1) as 'Transaction Date'
Resident Periods
While [Start date] + IterNo() - 1 < [End date];

drop Table Periods;