Create a flag based on conditions from variables on 3 different tables

[Jacob_Weig]

Hello,

I need to create a flag based on variables on 3 different tables:

Canonical_date belongs in TableA, cumulative_due_amount in TableB, and cumulative_paid_amount in TableC

I basically need to somehow build in the data load the table below:

TableA:

Canonical date

due date

paid date

TableB:

id

Due date

cumulative_due amount

TableC:

id

paid_date

cumulative paid amount

I need to create a create a flag with the condition:

If cumulative_due_amount>cumulative_paid_amount then 1 else 0

Thank you!

Marcela

[sidhiq91]

@Jacob_Weig  Please see the below script that I have used in the Script Editor. I have attached the output as well. Please let us know if it has resolved your issue.

NoConcatenate
TableA:
Load * Inline [
Id, Due_Date, Cumulative_Due_Amt
1,01/07/2022,800
2,02/07/2022,2000
3,03/07/2022,2000
4,04/07/2022,3000
];

Left join (TableA)

TableB:
Load * Inline [
Id, Paid_Date, Cumulative_Paid_Amt
1,01/07/2022,1000
2,02/07/2022,1800
3,03/07/2022,3000
4,04/07/2022,2500
];

NoConcatenate
Temp:
LOad *,
if(Cumulative_Due_Amt>Cumulative_Paid_Amt,1,0) as Flag,
AutoNumber(Due_Date&''&Paid_Date) as Date_ID
Resident TableA;

LEft join (Temp)
TableC:
Load Canonical_Date,
AutoNumber(Due_Date&''&Paid_Date) as Date_ID

INline [
Canonical_Date, Due_Date, Paid_Date
01/07/2022,01/07/2022,01/07/2022
15/07/2022,02/07/2022,02/07/2022
16/07/2022,03/07/2022,03/07/2022
17/07/2022,04/07/2022,04/07/2022
];

Drop table TableA;

Exit Script;