topic Re: How do I compare two tables for matching and non-matching results. in QlikView

How do I compare two tables for matching and non-matching results.

Wed, 25 Nov 2020 16:16:04 GMT

I am new to QlikView and want to do something that is extremely simple in SQL, but I am having problems trying to figure out the QlikView syntax.

I have to lists of names that come in from two separate sources (in the example they are both coming in from an excel sheet, but in live this will not be the case).

I want to make one table that holds only the ‘GroupMembers’ entries that match to names in ‘AllUsers’ and another table that holds all ‘‘GroupMembers’ entries that don’t match and names within the ‘AllUsers’ table.

Can I do this with joining the two resident tables and/or a where statement?

I have:

AllUsers:
LOAD distinct @1 as SPUsers
FROM

(biff, no labels, table is AllUsers$);

GroupMembers:
LOAD distinct @1 as GroupMembers
FROM

(biff, no labels, table is GroupMembers$);

I tried variations on the following to no effect!

UsersFound:
LOAD SPUsers ,'Table1' as Table Resident AllUsers;
CONCATENATE
LOAD GroupMembers ,'Table2' as Table Resident GroupMembers;
INNER JOIN (UsersFound)
LOAD GroupMembers
RESIDENT UsersFound; //should be users in table 1 and 2

UsersNotFound:
LOAD SPUsers ,'Table1' as Table Resident AllUsers;
CONCATENATE
LOAD GroupMembers ,'Table2' as Table Resident GroupMembers;
RIGHT JOIN (UsersNotFound)
LOAD GroupMembers
RESIDENT UsersNotFound; //should be users in table 2 but not 1

What I am trying to do here is make two new tables from the original tables, one holding all matched, and one holding all unmatched names.

Am I even close? Any help would be appreciated.

Re: How do I compare two tables for matching and non-matching results.

vinieme12 — Wed, 09 Aug 2017 13:49:36 GMT

try as below

AllUsers:
LOAD distinct @1 as SPUsers
FROM

(biff, no labels, table is AllUsers$);

GroupMembersE: // GroupMembers that exist in AllUsers
LOAD distinct @1 as GroupMembers_E
FROM

(biff, no labels, table is GroupMembers$)

where Exists(@1,SPUsers);

GroupMembersNE: // GroupMembers that Do NOT exist in AllUsers
LOAD distinct @1 as GroupMembers_NE
FROM

(biff, no labels, table is GroupMembers$)

where NOT Exists(@1,SPUsers);

Re: How do I compare two tables for matching and non-matching results.

Wed, 09 Aug 2017 14:15:03 GMT

I did try starting out by using Exists, but this is still saying 'Field not found - <SPUsers>' at compile time.

My full script is now:

AllUsers:
LOAD distinct @1 as SPUsers
FROM

(biff, no labels, table is AllUsers$);

GroupMembers:
LOAD distinct @1 as GroupMembers
FROM

(biff, no labels, table is GroupMembers$);

GroupMembersE: // GroupMembers that exist in AllUsers
LOAD distinct @1 as GroupMembers_E
FROM

(biff, no labels, table is GroupMembers$)
where Exists(@1,SPUsers);

GroupMembersNE: // GroupMembers that Do NOT exist in AllUsers
LOAD distinct @1 as GroupMembers_NE
FROM

(biff, no labels, table is GroupMembers$)
where NOT Exists(@1,SPUsers);

But it still errors on the SPUsers, in the Exists statement, which I assume should be saved by the time I am trying to use it.

Re: How do I compare two tables for matching and non-matching results.

sasiparupudi1 — Thu, 10 Aug 2017 08:22:43 GMT

An example solution may be like below

GroupMembers:

Load * inline
[
GroupMembers
Sam
Ant
George
Antonio
];

AllUsers:

Load * inline
[
SPUsers
Sam
Ant
Tim
Greg
George
];

GroupMembers_Filtered:
Load GroupMembers as ExistingMembers
Resident GroupMembers
Where Exists(SPUsers,GroupMembers);

GroupMembers_Filtered1:
Load GroupMembers as NONExistingMembers
Resident GroupMembers
Where Not Exists(SPUsers,GroupMembers);

Re: How do I compare two tables for matching and non-matching results.

effinty2112 — Thu, 10 Aug 2017 08:31:31 GMT

Hi Garry,

Something like:

AllUsers:

LOAD distinct @1 as SPUsers

FROM

(biff, no labels, table is AllUsers$);

GroupMembersInAllUsers:

LOAD distinct @1 as GroupMembersInAllUsers

FROM

(biff, no labels, table is GroupMembers$)

WHERE Exists(SPUsers,@1);

GroupMembersNotInAllUsers:

LOAD distinct @1 as GroupMembersNotInAllUsers

FROM

(biff, no labels, table is GroupMembers$)

WHERE Not Exists(SPUsers,@1);

Cheers

Andrew

Re: How do I compare two tables for matching and non-matching results.

effinty2112 — Thu, 10 Aug 2017 08:39:03 GMT

Hi Garry,

A mapping load might give a neat alternative. something like:

MappingAllUsers:

Mapping

LOAD distinct

'Y'

FROM

(biff, no labels, table is AllUsers$);

GroupMembersInAllUsers:

LOAD

DISTINCT

@1 as Member,

Applymap('MappingAllUsers',@1,'N') as InGroup

FROM

(biff, no labels, table is GroupMembers$)

Re: How do I compare two tables for matching and non-matching results.

Thu, 10 Aug 2017 09:25:50 GMT

Thanks for all the replies, I did get this working using Exists() as follows:

AllUsers:
LOAD distinct lower(replace(@1,'.',' ')) as SPUsers
FROM [\\192.168.0.60\c$\Logs\*.log]
(txt, codepage is 1252, no labels, delimiter is ',', msq);

GroupMembers:
LOAD distinct lower(@1) as Member
FROM [\\192.168.0.60\c$\Logs\groupmembers.csv]
(txt, codepage is 1252, no labels, delimiter is ',', msq);

GroupMembers_Found:
LOAD distinct Member as Members_E
Resident GroupMembers
where Exists(SPUsers,Member);

GroupMembers_NotFound:
LOAD distinct Member as Members_NE
Resident GroupMembers
where NOT Exists(SPUsers,Member);

This is the final version of the code, taking the data generated from a test server.

Re: How do I compare two tables for matching and non-matching results.

sasiparupudi1 — Thu, 10 Aug 2017 09:30:09 GMT

Great, please close this thread by selecting any helpful and a correct answer.