First values with conditions

[LoKi_asterix]

Hey folks, I’m dealing with a transactional table where the first entries are the initial investments, and the rest may be other types of transactions. But with the transaction codes 'TC00402' quite narrows it.

I want to pull out just those initial investments by looking at the earliest date for each account. Some accounts only have one record, while others have multiple entries on the same date.

How would you go about separating these in the script? Would love any tips or approaches!

[PrashantSangle]

can you provide some sample data with expected o/p??

regards,

Prashant Sangle

[LoKi_asterix]

@PrashantSangle 

CusID	TransCode	Date	Amount
aaa	136	01/01/24	100
aaa	136	01/05/25	50
aaa	420	06/09/25	100
bbb	61	01/01/25	120
ccc	61	01/05/23	75
zzz	136	01/01/23	500
zzz	61	02/09/25	15
ddd	420	02/02/25	10
ddd	420	02/02/25	20
ddd	420	02/02/25	20
ddd	420	09/09/25	40
eee	420	07/09/25	5
eee	420	08/10/25	5
eee	420	08/11/25	5

This is how the results are supposed to work based on the business rules.

For CustID aaa, transaction code 136 is the correct one to use. So the principal is 100 + 50 = 150.
Code 420 is ignored because it represents an incorrect narration in this case.

For CustID bbb and CustID ccc, code 61 is the valid transaction type, giving principals of 120 and 75 respectively.

CustID zzz has two valid transaction types: 136 (cash deposit) and 62 (transfer). Since both are legitimate, both amounts are included, giving a total of 515.

Code 420 usually represents balance brought forward and can appear multiple times.
When all 420 entries for a customer fall on the same date, their amounts should be added together.
That’s why CustID ddd ends up with 10 + 20 + 20 = 50.

For CustID eee, code 420 appears on different dates, so only the first occurrence is taken and the rest are ignored.

[hanna_choi]

Hello @LoKi_asterix 

How about this ways?

After extracting MIN values by CUSID and YEAR
Sum the corresponding Amount for the Min value

We got the desired value.

[ Load Script ]