Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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.

3 Replies
fvelascog72
Valued Contributor

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;

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

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.

Community Browser