Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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):
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
Try this:
sum(aggr(sum(distinct pieces), claim))
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!