Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
coloful_architect
Creator II
Creator II

set expression to only show lowest price from one particular company

I have a table with these fields 

product code, price, product type, company, price

for same product type, there are different product codes by different companies.

I want to build a table chart to show all the products by company x as long as it has the lowest price

for example, if the conceptual raw table is like 

Product Code Price Product Type company 
A 1 apple x
B 3 apple y
C 0.5 apple z
D 3 orange x
E 5 orange y
F 2.1 berry x
G 7 berry y

 

the table I want to render is 

Product Code Price Product Type company 
D 3 orange x
F 2.1 berry x

 

since company x only has the lowest prices on orange and berry, then the table only picks 2 products out.

what set expression at price  I need to build.

pls advise.

thanks 

Labels (1)
5 Replies
Chanty4u
MVP
MVP

Try this

=aggr(if(price = min(total <Product Type> price), Product Code), Product Type, company)

 

Rohan
Specialist
Specialist

Hi,

For your scenario, you should try using Aggr() with min() in the dimension column. like :

Aggr(Min(Price),Product_Type,Company).

Try this & revert if any issues.

 

Regards,

Rohan.

Ahidhar
Creator III
Creator III

try this

min({<company={'x'}>}aggr(If(Price = min(total <[Product Type]> Price) and company='x',Price),[Product Type],company))

coloful_architect
Creator II
Creator II
Author

Thanks Ahidahr, what if I want to add one more condition to exclude those data rows that company x is exclusive? 

if company x only produces one product, its price is by default the lowest. I dont want my table to show those data.

so how do we exclude that condition based on your set expression? please advise.

 

Rohan
Specialist
Specialist

Hi @coloful_architect ,

Generate a flag from load script, where you flag exclusive products. by :

left join(Data)

Load Product_Code,

Count(distinct Company) as Count.

Resident Data group by Product_Code;

Then in set analysis, Exclude Count-={"1"}. Hope this helps.

 

Regards,

Rohan.