Peek and Previous Function to Fill Missing Values

[aditya_k_d]

Hi All,

I have sample data as shown below

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

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

Reporter ID	Transaction ID	Transaction Date	Sales
R1	T1	1-Jan-19	100
R1	T1	2-Jan-19	100
R1	T1	3-Jan-19	100
R1	T1	4-Jan-19	100
R1	T1	5-Jan-19	80
R1	T1	6-Jan-19	80
R1	T1	7-Jan-19	80
R1	T1	8-Jan-19	80
R1	T1	9-Jan-19	80
R1	T1	10-Jan-19	60
R1	T1	11-Jan-19	60
R1	T1	12-Jan-19	60
R1	T1	13-Jan-19	60
R1	T1	14-Jan-19	60
R1	T1	15-Jan-19	40

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

Any help is appreciated.

Thanks

[mrybalko]

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;