Scripting Question

[xyz_1011]

Hi all,

the following is an extract of my data:

Contract	Period	Discount	Discount To Be
1234	2023-Q3	10	10
1234	2023-Q4	15	15
1234	2024-Q1		15
12	2023-Q3	25	25
12	2023-Q4		25
12	2024-Q1		25
12	2024-Q2	15	15

There are cases, where Discount is not populated. In these cases i (always) want to take the Discount of the last Period for a given Contract. I have tried Previous() but that only appears to be working for cases like contract 1234 (one missing record only), but it does not seem to work for Contracts with multiple gaps (like contract 12)

Any hints and help is much appreciated. Thanks!

[LRuCelver · Accepted solution]

Assuming the first entry for ever contract always has a Discount, you can use this:

//	Load Data and format '' to Null
Data:
NoConcatenate Load
	Contract,
    Period,
    If(Discount <> '', Discount) as Discount,
    "Discount To Be"	
Inline [
    Contract,	Period,		Discount,	Discount To Be
    1234,		2023-Q3,	10,			10
    1234,		2023-Q4,	15,			15
    1234,		2024-Q1,	,			15
    12,			2023-Q3,	25,			25
    12,			2023-Q4,	,			25
    12,			2024-Q1,	,			25
    12,			2024-Q2,	15,			15
];

//	Fill missing Discounts
Final:
NoConcatenate Load
	Contract,
    Period,
    If(IsNull(Discount), Peek('Discount'), Discount) as Discount,
    "Discount To Be"
Resident Data
Order By Contract, Period;

Drop Table Data;