Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I am trying to add a table to my document that sums all of the loan amounts for a given person and creates a field that contains that sum for each of the rows that is connected to that person. I'm trying to add it to my script but i keep getting an "invalid expression" error. This is what I have:
Tmp:
LOAD
loan_no,
sum(amount) as personTotal
RESIDENT ACCOUNTS
where Employee = 'Non Employee'
GROUP BY Person;
In the end I would like to have something that looks like this:
loan_no | Person | amount | personTotal |
---|---|---|---|
123 | Matt | 500 | 950 |
456 | Matt | 450 | 950 |
789 | John | 800 | 1050 |
1011 | John | 250 | 1050 |
Is this possible?
Thank you!
It's because loan_no and Person have different groupings. What you could do is use
Tmp:
LOAD
Person,
sum(amount) as personTotal
RESIDENT ACCOUNTS
where Employee = 'Non Employee'
GROUP BY Person;
This new table will then associate with the original table "ACCOUNTS" on field "Person".
Or merge in via a mapping load + applymap.
Hi,
You must group by loan_no, person and amount.
The invalid expression is caused because you don´t use an aggregation with loan_no.
Something like this:
Tmp:
LOAD
loan_no,
Person,
amount
sum(amount) as personTotal
RESIDENT ACCOUNTS
where Employee = 'Non Employee'
GROUP BY loan_no,Person,amount;
Hi try like this
Tmp:
LOAD
only(loan_no) as loan_no,
sum(amount) as personTotal
RESIDENT ACCOUNTS
where Employee = 'Non Employee'
GROUP BY Person;
Regards
ASHFAQ
It's because loan_no and Person have different groupings. What you could do is use
Tmp:
LOAD
Person,
sum(amount) as personTotal
RESIDENT ACCOUNTS
where Employee = 'Non Employee'
GROUP BY Person;
This new table will then associate with the original table "ACCOUNTS" on field "Person".
Or merge in via a mapping load + applymap.