topic Re: Reference Table in QlikView

Reference Table

brf10043 — Wed, 25 Nov 2020 16:16:04 GMT

So I have a table with support tickets. for each support ticket there is an Submitter Group. I also have a Reference table of Resolving Groups that has a RA Type which indicates which Resolving Agencies are Helpdesk and which ones are Non Help desk. Example below.

So There is no flag column in the Submitter Group that indicates if the Submitter Group is an Helpdesk RA or not. Also, in the Submitter Group data there are Groups that are not listed in the Reference table of Resolving Agencies.

These table a currently linked in the data model.

I am attempting to write an expression that counts Submitter Groups in the support ticket table that do not exist in the Reference Resolving Agency data. I can count tickets where the Submitter Group is an Helpdesk Resolving Agency and I can count Submitter Groups where they are Non Helpdesk Resolving Agencies, but I can't get to a count where the Submitter Resolving Agency didn't match a Resolving Agency in the Reference Resolving Agency table.

Ideas?

Re: Reference Table

sunny_talwar — Fri, 27 Jul 2018 18:17:46 GMT

What if you do this... count everything and subtract those groups where they don't have a RA Type?

Count([Submitter Groups]) - Count({<[RA Type] = {"*"}>}[Submitter Groups])

Re: Reference Table

brf10043 — Fri, 27 Jul 2018 18:26:48 GMT

Could the problem be that I am linking the two tables together and maybe should be outer joining the Xref table tot he Ticket table so I can actually get Null values?

Re: Reference Table

brf10043 — Fri, 27 Jul 2018 18:39:11 GMT

Thanks Sunny! Once again. That seems to have worked.