Expression as a group by in Qlik

[Phara]

Hello, 

Here is an example, I want an expression that will identify only Supplier A and C which are respecting the following criterias :

1. They are using imperatively the Service charge key is ' 60-financial'

2. They have others Service charge key

The suppliers B and D do not respect the first criteria, so they have to be ignored.

Supplier name	Service charge key	Amount
Supplier A	52-Location	1000 $
Supplier B	54-contract	4000 $
Supplier C	60-financial	5000 $
Supplier D	54-contract	7000 $
Supplier C	54-contract	800 $
Supplier A	60-financial	9000 $

[diegozecchini]

Hi!
you can create a calculated expression to identify suppliers who meet both conditions

Use 60-financial as a Service charge key.
Have other Service charge key entries.

Use Qlik's aggr() function to group by Supplier name and evaluate the conditions.

If(
Count({< [Service charge key] = {'60-financial'} >} DISTINCT [Service charge key]) > 0 AND --> Ensures that the supplier uses the 60-financial service charge key.
Count(DISTINCT [Service charge key]) > 1, --> Ensures that the supplier has other service charge keys in addition to 60-financial.
'Qualified',
'Not Qualified'
)

If condition classifies suppliers as 'Qualified' or 'Not Qualified' based on these criteria.

If you only want to display Supplier A and Supplier C in a visualization:
Add the above expression as a calculated dimension.
Use a filter or dimension condition to include only 'Qualified' suppliers.

Sample Output:
After applying the expression, your table will look like this:

Supplier name Status
Supplier A Qualified
Supplier B Not Qualified
Supplier C Qualified
Supplier D Not Qualified
This approach will help you dynamically identify suppliers that meet your criteria directly within Qlik.

[Qrishna]

There are fw ways to achieve this:

1 Backend Solution

Create a flag in the load script: 

Data:
LOAD [Supplier name],
[Service charge key],
Amount,
if(Match([Supplier name], 'Supplier A', 'Supplier C') or Match([Service charge key], '60-financial'), 1, 0) As Indetification_Flag
FROM
[https://community.qlik.com/t5/New-to-Qlik-Analytics/Expression-as-a-group-by-in-Qlik/td-p/2498674]
(html, codepage is 1252, embedded labels, table is @1);

in the table: =Aggr(Only({$<Indetification_Flag={1}>}[Supplier name]), [Supplier name]) use as dimentsion:

2. Front End Solution: 

use = if(Match([Supplier name], 'Supplier A', 'Supplier C') or Match([Service charge key], '60-financial'), [Supplier name])  as dimentsion:

[Kushal_Chawda]

@Phara  try below expression

=sum({<[Supplier name]=p({<[Service charge key]={'60-financial'}>})>}Amount)