Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Kushal_Chawda

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

vishsaggi
Champion III
Champion III

What is your expected output?

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

effinty2112
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

Kushal_Chawda

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

Not applicable
Author

Thank you!

Not applicable
Author

Thanks!

Not applicable
Author

Thanks!

Not applicable
Author

Thanks!