Getting the Previous Contract’s Date and Amount

[LoKi_asterix]

Hi all,

I’m trying to figure out how to pull the previous contract’s details from a master table. Here’s the setup:

CustomerID,ContractID,TransDate, Disbursed Amount

Every time a contract is completed, a new ContractID is created. What I need is:

For any current contract, get the date and amount of the previous contract.

If a customer has multiple past contracts, I only want the most recent one before the current contract.

I’d love to see how you’d approach it, either in the script or using chart expressions. Any tips, examples, or best practices would be great.

Thanks!

[Rushi21]

Hi loki,

I was able get the correct O/p with the followig script, might not be most optimal solution but worked for the sample data.

//___________________________________________________Script________________________//

NoConcatenate
Contract:
Load
CustomerID,
ContractID,
Date(Date#(TransDate,'DD-MM-YYYY'),'DD/MM/YYYY') as TransDate,
sum("Disbursed Amount") as "Disbursed Amount"
group by CustomerID,ContractID,TransDate;
Load * INLINE [
CustomerID, ContractID, TransDate, Disbursed Amount
Apple,112,01-02-2024,2000
Apple,113,01-01-2025,4000
Apple,114,01-06-2025,100000
Apple,100,01-01-2023,61000
Apple,116,01-11-2025,75000
];

left Join(Contract)
Temp:
Load
CustomerID,
max(TransDate) as Flag
Resident Contract group by CustomerID;

NoConcatenate
Final:
Load
*
Resident Contract where Flag=TransDate;

Left Join(Final)
Load
CustomerID,
FirstSortedValue(ContractID,-TransDate,2) as PreviousContract
Resident Contract group by CustomerID;
// Drop Table Contract;

Left Join(Final)
Load
ContractID as PreviousContract,
TransDate as PreviousDate,
"Disbursed Amount" as PreviousAmount
Resident Contract;

Drop Table Contract;

Exit Script;