Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

create field in parent table that sums values from child table

Hi, Everyone.

I have 2 tables. And they are linked by a field called 'CustomerID'. Here's how the tables look like:

Table 1

CustomerIDCustomerNamePrepaidCredits
1John Doe30000
2Peter Smith70000
3Emily Taylor20000

Table 2

TransactionIDCustomerID

TransactionAmt

TransactionCharge

1110000250
2220000500
315000125
4310000250
525000125
635000125

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.

2 Replies
Anonymous
Not applicable
Author

See attached

its_anandrjs

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

Query.png

Regards

Anand