Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, Everyone.
I have 2 tables. And they are linked by a field called 'CustomerID'. Here's how the tables look like:
Table 1
CustomerID | CustomerName | PrepaidCredits |
---|---|---|
1 | John Doe | 30000 |
2 | Peter Smith | 70000 |
3 | Emily Taylor | 20000 |
Table 2
TransactionID | CustomerID | TransactionAmt | TransactionCharge |
---|---|---|---|
1 | 1 | 10000 | 250 |
2 | 2 | 20000 | 500 |
3 | 1 | 5000 | 125 |
4 | 3 | 10000 | 250 |
5 | 2 | 5000 | 125 |
6 | 3 | 5000 | 125 |
I would like to create the following new fields in Table 1:
TotalTransAmt: sum of TransactionAmt per customer
TotalTransCharge: sum of TransactionCharge
Balance: PrepaidCredits - TotalTransAmt - TotalTransCharge
Status: If Balance is less than amount specified in an InputBox, returns 'Alert' and 'OK' if Balance is equal or greater than the amount specified in the InputBox
Is this possible?
Thanks in Advance.
See attached
Hi,
Try this way also join this two table on the basis of the CustomerID and then do aggregation in another table with your formula and join that table also. See the script for that
Table1:
LOAD * INLINE [
CustomerID, CustomerName, PrepaidCredits
1, John Doe, 30000
2, Peter Smith, 70000
3, Emily Taylor, 20000
];
Left Join(Table1)
LOAD * INLINE [
TransactionID, CustomerID, TransactionAmt, TransactionCharge
1, 1, 10000, 250
2, 2, 20000, 500
3, 1, 5000, 125
4, 3, 10000, 250
5, 2, 5000, 125
6, 3, 5000, 125
];
Left Join(Table1)
LOAD
CustomerID,
Sum(TransactionAmt) as SUM_TransactionAmt,
Sum(TransactionCharge) as SUM_TransactionCharge,
Sum(PrepaidCredits) as Prepaidsum,
Sum(DISTINCT PrepaidCredits) - Sum(DISTINCT TransactionAmt) - Sum(TransactionCharge) as Balance
Resident Table1
Group By CustomerID;
LET vAmount = 15000;// And create this variable also
And in the straight table chart
Dim1:- CustomerID
Dim2:- CustomerName
Expr1:- PrepaidCredits
Expr2:- SUM_TransactionAmt
Expr3:- SUM_TransactionCharge
Expr4:- Balance
Expr5:- if ( Balance < vAmount , 'Alert' , 'OK' )
And you get
Regards
Anand