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

Cant figure out why this doesn't work

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_noPersonamountpersonTotal
123Matt500950
456Matt450950
789John8001050
1011John2501050

Is this possible?

Thank you!

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

3 Replies
fvelascog72
Partner - Specialist
Partner - Specialist

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
Champion III
Champion III

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

Not applicable
Author

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.