Count if Value exists in Table

[Evgenij]

Hello all,

I am new to Qlik and trying to create a report where tickets are only counted if the ticket group exists in another table.

Group_ID	My_Groups
1	Group_1
2	Group_2

Ticket_ID	Ticket_Group
123	Group_6
234	Group_2
345	Group_4
456	Group_3

Tried to solve it with "match" but that doesn't work.

=if(
     match(Ticket_Group, My_Groups), 
     count(distinct(Ticket_ID))
   )

Does anyone have a suggestion for me on how to solve this?

br, Evgenij

[rubenmarin]

Hi  @Evgenij, there is an Exists() function that can check if any value exists in another field, and it will be better if you flag those records in script.

To flag records add a field in tickets table as: If(Exists('Ticket_Group', My_Groups, 1,0) as isExistingGroup

Then for count you only need: Count({<isExistingGroup={1}>} Ticket_ID)

[Evgenij]

Hello Ruben,

i'm getting "Field" 'My_Groups' not found"-Error if i try to flag the data like suggested by you.

[rubenmarin]

Hi, that message is telling you that the source table doesn't has any field with that name, use the name of the field on the source table.