Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Not applicable

Re: Cant figure out why this doesn't work

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
Highlighted
Partner
Partner

Re: Cant figure out why this doesn't work

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;

Highlighted

Re: Cant figure out why this doesn't work

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

Highlighted
Not applicable

Re: Cant figure out why this doesn't work

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