Issue with join

[#1]

Hi every one,

i am trying to join the 3 datasets as below:

JOIN (UserAccess_Master) LOAD DISTINCT USERID,Unit as Unit1  RESIDENT UserAccess_Area;

JOIN (UserAccess_Master) LOAD DISTINCT USERID,Unit as Unit1 RESIDENT UserAccess_LIMITDEPARTMENT;

JOIN (UserAccess_Master) LOAD DISTINCT USERID,Unit as Unit1 RESIDENT UserAccess_LIMITSUBDEPARTMENT;

UserAccess_Area:

LIMITAREA,USERID,Unit

NORTH,user1,N-Fin-Sales-unit1

NORTH,user1,N-Fin-Sales-unit2

NORTH,user1,N-Fin-Sales-unit3

SOUTH,user2,S-Fin-Sales-unit

UserAccess_LIMITDEPARTMENT:

LIMITDEPARTMENT,USERID,Unit

WEST-SALES,admin,W-Fin-Sales-unit

WEST-SALES,user4,W-Fin-Sales-unit

UserAccess_LIMITSUBDEPARTMENT:

LIMITSUBDEPARTMENT,USERID,Unit

W-FIN-SALES,user1,W-Fin-Sales-unit

UserAccess_Master:

USERID

admin

user1

user2

user4

But the result table has 2 rows marked as X coming blank:

USERID,Unit1

admin,                                                       X

admin,W-Fin-Sales-unit

user1,N-Fin-Sales-unit1

user1,N-Fin-Sales-unit2

user1,N-Fin-Sales-unit3

user1,W-Fin-Sales-unit

user4,                                                            X

user4,W-Fin-Sales-unit

user2,S-Fin-Sales-unit

Suggest any solution.

Thanks,

Rahul

[#2 · Accepted solution]

Hi Rahul,

Initially UserAccess_Master doesn't contain a Unit1 field.

So performing an outer join on any of your 3 other tables will create null values in the created Unit1 field when there's no matching user in both tables.

Now you can get your result by checking script1 attached.

But I assumed that this is just a sample script and that you may need to keep in your real script the concept of joining tables together, so you can check script2 for that.

Hope this will help you apply the required steps in your model.

[#3]

Thanks for reply. it worked. You Rock 🙂