find records with same dimensions

[SL2]

hi everyone! I am new to Qlik sense, can anyone help me with this?

my table is looking like

and I want to find records with same [transaction date] and same [staff ID] but having more than 1 [cost center code],

so here entry ID 1 & 2 are records I want, and I expect to create a new field [entry I am looking for] to distinguish these entries, i.e. 

can anyone know how to figure this out? Thanks!

[jbhappysocks · Accepted solution]

Hi!

Based on your example:

if(Count(total <[staff ID], [transaction date]> [cost center code])>1,'Yes','No')

(Entry 6 and 7 are also supposed to be highlighted right?)

If you want to create a table with only the Yes entries, like this:

You change it to if(Count(total <[staff ID], [transaction date]> [cost center code])>1,'Yes',Null())

and change sum(Amount) to if(Count(total <[staff ID], [transaction date]> [cost center code])>1,sum(Amount),Null())

and exclude Zero values.

If you actually want to create a new field [Entry I am looking for] and not just have it as a calculation in the table, you can add something like this to your script:

Join (data)
Load
"transaction date",
"staff ID",
if(count(DISTINCT "cost center code")>1,'Yes','No') as [Entry I am looking for]
Resident data Group By "transaction date","staff ID";

This will give you a field you can filter on.

[SL2]

Thanks! I just find out if there are multiple records with same [transaction date], same [staff ID], same [cost center code] and that record would also be marked as 'Yes'. To avoid that we can make a slight change:

if(Count(distinct total <[staff ID], [transaction date]> [cost center code])>1,'Yes','No')