Function To Compare Values of two ID's from two different Datasets

[#1]

I have a situation where I need to get the Intersection of values from two datasets where no fields is common, however the some of the Values in ID's are common for the two datasets. Is there any specific function or method that any one can think of to get the Intersecting values to list out in a straight Table. I tried using Match, but not sure if that helps.

Ex: 

SalesEmployeeID

1002

1003

1004

1005

1006

MarketingEmployeeID

1004

1005

1006

1007

1008

1010

NOTE: I cannot Join the Tables at the Script level, as I will be getting ambiguous results due to the Loop.

[pgrenier]

Hello Ahmed,

How about creating an extra field in the Marketing and Sales tables that would indicate whether the employee is also in the other table. Then, you can filter out the employees as you wish using the IsAlsoInSales or IsAlsoInMarketing field. Here would be an example script:

SalesEmployees:

LOAD * Inline [

SalesEmployeeID

1002

1003

1004

1005

1006];

MarketingEmployees:

LOAD *, If(Exists(SalesEmployeeID, MarketingEmployeeID), 1, 0) as IsAlsoInSales;

LOAD * Inline [

MarketingEmployeeID

1004

1005

1006

1007

1008

1010];

Left Join(SalesEmployees)

LOAD SalesEmployeeID, If(Exists(MarketingEmployeeID, SalesEmployeeID), 1, 0) as IsAlsoInMarketing

Resident SalesEmployees;

Regards,

Philippe

[jagan]

Hi,

Try using Mapping Load like this

SalesEmployeeMap:
MAPPING LOAD 
SalesEmployeeID,
SalesEmployeeID AS Status
INLINE [
SalesEmployeeID
1002
1003
1004
1005
1006];

SalesEmployee:
LOAD 
SalesEmployeeID
INLINE [
SalesEmployeeID
1002
1003
1004
1005
1006];

MarketingEmployee:
LOAD
MarketingEmployeeID,
ApplyMap('SalesEmployeeMap', MarketingEmployeeID, 'N/A') AS IsExists
INLINE [
MarketingEmployeeID
1004
1005
1006
1007
1008
1010];

Use IsExists as a List box which contains all the intersecting Ids..

Regards,

Jagan.