topic Re: Compare 2 tables and flag if the values match/don't match in QlikView

Compare 2 tables and flag if the values match/don't match

Wed, 25 Nov 2020 16:16:04 GMT

Follow up on a recent question...

Load * inline [

User, Salary

A, 20

B, 10

C, 50

]

Load * inline [

Username, Log Date

B, 12,6,2017

C, 5/4/2016

]

I want a new field, called UserType, that compares the two lists of Users. First list is Users, but second is Usernames, but I want when these values are the exact same. If in the first and second list, then call it UserType 'A'. If not in the second list, call it UserType 'B'

Any ideas?

Thanks

Re: Compare 2 tables and flag if the values match/don't match

krishnacbe — Mon, 07 Aug 2017 20:53:39 GMT

Try this

A:
Load * inline [
User, Salary, UserType
A, 20, A
B, 10, A
C, 50, A
];
Inner join
B:
Load * inline [
User, Log Date
B, 12,6,2017
C, 5/4/2016
];
noconcatenate
B1:
Load * inline [
User, Log Date
B, 12,6,2017
C, 5/4/2016
];
A1:
Load * inline [
User, Salary, UserType
A, 20, B
B, 10, B
C, 50, B
]
Where not Exists(User);
drop Table B1;

Re: Compare 2 tables and flag if the values match/don't match

vishsaggi — Mon, 07 Aug 2017 20:55:32 GMT

What is your expected output?

Re: Compare 2 tables and flag if the values match/don't match

vishsaggi — Mon, 07 Aug 2017 20:58:26 GMT

May be this?

UserList:

Load * inline [

Username, Log Date

B, 12,6,2017

C, 5/4/2016

];

User:

Load * inline [

User, Salary

A, 20

B, 10

C, 50

];

EmployeeName:

Load User as Employee, Salary,

IF(Exists(Username, User), 'A', 'B') AS GroupType

Resident User;

DROP TABLES User, UserList;

Re: Compare 2 tables and flag if the values match/don't match

effinty2112 — Mon, 07 Aug 2017 21:12:49 GMT

Hi Aaron,

Try:

Users:

Load * inline [

User, Salary

A, 20

B, 10

C, 50

];

UserNames:

Load * inline [

Username, Log Date

B, 12,6,2017

C, 5/4/2016

];

AllUsers:

LOAD User as UserRef, 'A' as UserType Resident Users;

LOAD Username as UserRef, 'B' as UserType Resident UserNames Where not Exists(User,Username);

Gives:

UserRef	UserType
A	A
B	A
C	A

Regards

Andrew

Re: Compare 2 tables and flag if the values match/don't match

Kushal_Chawda — Tue, 08 Aug 2017 10:23:21 GMT

Users:

Load * inline [

User, Salary

A, 20

B, 10

C, 50

];

Left Join(Users)

LOAD Username as User,[Log Date],1 as Flag;

Load * inline [

Username, Log Date

B, 12/6/2017

C, 5/4/2016

];

Final:

NoConcatenate

LOAD *,

if(Flag=1,'A','B') as UserType

Resident Users;

DROP Table Users;

Re: Compare 2 tables and flag if the values match/don't match

Tue, 08 Aug 2017 14:22:13 GMT

Thank you!

Re: Compare 2 tables and flag if the values match/don't match

Tue, 08 Aug 2017 14:22:21 GMT

Thanks!

Re: Compare 2 tables and flag if the values match/don't match

Tue, 08 Aug 2017 14:22:57 GMT

Thanks!

Re: Compare 2 tables and flag if the values match/don't match

Tue, 08 Aug 2017 14:23:14 GMT

Thanks!