topic Re: Function To Compare Values of two ID's from two different Datasets in QlikView

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

Tue, 24 Sep 2013 21:48:36 GMT

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.

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

pgrenier — Tue, 24 Sep 2013 22:24:38 GMT

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

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

jagan — Wed, 25 Sep 2013 01:55:44 GMT

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.