Cant figure out why this doesn't work

[#1]

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!

[fvelascog72]

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;

[ashfaq_haseeb]

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

[#4 · Accepted solution]

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.