topic Re: Only show specific Data in Table in QlikView

Only show specific Data in Table

Thu, 22 May 2014 09:28:47 GMT

Hello Community,

I have the following problem with a table Diagramm.

I got different collumns with a Costumer, ID (for the Product he bought), and the Price. Each Costumer could have bought more Products, so there is a new line for every Product

Costumer	ID	Price
Shaun Williams	55	400
Shaun Williams	66	500
Shaun Williams	77	300
Maria Franklin	11	777
Maria Franklin	22	200
Maria Franklin	33	350
Maria Franklin	44	378
Maria Franklin	55	400
Maria Franklin	66	500
Albert Einstein	1	152
Albert Einstein	13	757

Now, I want to make a table, where only the most expensive Product is listed the costumer bought.

Costumer	ID	Price
Shaun Williams	66	500
Maria Franklin	11	777
Albert Einstein	13	757

I tried to set the Dimension Costumer and the Functions:

If(rank(Price)=1,ID,Null())

Aggr(If(rank(Price)=1,Price),Price)

But then I only get a table, where everything is Null() except for the highest Price in the Data.

How can I filter the Data so that every Costumer is listed like in the second table?

Thanks a lot in advance!

Alex

Re: Only show specific Data in Table

Thu, 22 May 2014 10:41:53 GMT

What you want to do if you will have more than one product with this max price for each customer?

Re: Only show specific Data in Table

Thu, 22 May 2014 12:29:34 GMT

Hey Dariusz,

if there are more products with a max price, than there should be an extra row or more if needed.

Re: Only show specific Data in Table

satishkurra — Thu, 22 May 2014 12:33:23 GMT

Use the below expression:

=if(Aggr(Rank(Sum(Price)), Costumer) =1 , Costumer)

Re: Only show specific Data in Table

Thu, 22 May 2014 12:55:43 GMT

So,

use Costumer and ID as dimensions

and expression:

if(Price=max(TOTAL <Costumer> Price),Price)

Re: Only show specific Data in Table

Thu, 22 May 2014 12:59:23 GMT

Thanks a lot for your Help Dariusz, it works