topic Re: Count if Value exists in Table in QlikView

Count if Value exists in Table

Evgenij — Thu, 21 Jan 2021 11:50:23 GMT

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

Re: Count if Value exists in Table

rubenmarin — Thu, 21 Jan 2021 11:58:14 GMT

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)

Re: Count if Value exists in Table

Evgenij — Thu, 21 Jan 2021 12:19:25 GMT

Hello Ruben,

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

Re: Count if Value exists in Table

rubenmarin — Fri, 22 Jan 2021 08:09:05 GMT

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.