Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Sum some values of a table

I posted a similar question just a few days ago and made some advantages with your help.
Unfortunately I did not solve the problem completely.

I have loaded a table from a csv (sample data):

sdffs.jpg

I have loaded every line - not using "group by" as I try to solve it in the frontend.

* Counting the number of distinct claims is easy: count(distinct claim).
* Also to get the total of all costs is solvable:  sum(cost).

Question:
How can I get the sum of all pieces of each distinct claim?
The correct result is 13

What I need is something like sum(pieces for distinct claim)
Of course I know this syntax won´t work 😉

***
another approach is to solve it in the data model by using "group by"

What I have tried:

claim_tmp:
LOAD 
claim,
pieces,
costtype,
cost,
name
FROM mydata.qvd;

claim:
LOAD
claim_tmp.claim as claim,
MAX(claim_tmp.pieces) as pieces // could have used MIN also...
sum(claim_tmp.cost) as cost,
// how to load costtype here?
// how to load name here?
RESIDENT claim_tmp
GROUP BY claim_tmp.claim;

The problem is that I want to display all the data in a straight table in the frontend.
Using the MAX-function seems quite clever to me 😉
But how to get costtype and name as I am only allowed to use Aggragation functions when using GROUP BY?

The last problem is to get the sums of all costtypes.
The result should be:
Type 1: 240
Type 2: 200

As you can see I have tried a lot before asking - but I am still an newbe.

Thank you in advance for any hints!

Rainer

 

 

 

Labels (3)
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Try this:

sum(aggr(sum(distinct pieces), claim))

View solution in original post

2 Replies
Highlighted
Employee
Employee

Try this:

sum(aggr(sum(distinct pieces), claim))

View solution in original post

Highlighted
Contributor III
Contributor III

Hi Lisa,

thank you!
I have heard about the aggr-function but have never used it.
This seems to be a very mighty tool and I think this helps me a lot - though my head explodes when I read the docu 😉
I´ll play arround with this!