Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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;