Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.