Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

 

1 Solution

Accepted Solutions
effinty2112
Master
Master

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

View solution in original post

7 Replies
vinieme12
Champion III
Champion III

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);

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

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.

sasiparupudi1
Master III
Master III

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);

effinty2112
Master
Master

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

effinty2112
Master
Master

Hi Garry,

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

MappingAllUsers:

Mapping

LOAD distinct

@1

'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$)

Not applicable
Author

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.

sasiparupudi1
Master III
Master III

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