topic Scripting Question in App Development

Scripting Question

xyz_1011 — Tue, 27 Feb 2024 11:10:06 GMT

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!

Re: Scripting Question

LRuCelver — Tue, 27 Feb 2024 12:07:02 GMT

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;