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

[#1]

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

[krishnacbe]

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]

What is your expected output?

[vishsaggi]

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]

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

[Kushal_Chawda · Accepted solution]

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;

[#7]

Thank you!

[#8]

Thanks!

[#9]

Thanks!

[#10]

Thanks!