Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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;

Capture.JPG

View solution in original post

9 Replies
Highlighted
Partner
Partner

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;

Highlighted
Champion III
Champion III

What is your expected output?

Highlighted
Champion III
Champion III

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;

Highlighted
Master
Master

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
AA
BA
CA

Regards

Andrew

Highlighted
MVP
MVP

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;

Capture.JPG

View solution in original post

Highlighted
Not applicable

Thank you!

Highlighted
Not applicable

Thanks!

Highlighted
Not applicable

Thanks!

Highlighted
Not applicable

Thanks!