Some scripting assistance

[QFanatic]

Hi all,

I have a set of data - let say its data about employees with key Employee_number. Lets call this data set 1.

Then I want to check if this Employee Number exists in two other data sets - set 2 and 3. It can exists in either, or both.

I have just hit a wall with the scripting of this. The layout I'd like to get is the following.

Employee Number   Exists in data set 1   Exists in Data set 2

abc123                                    Yes                               No

def 789                                    Yes                              Yes

hjk456                                      No                               No

How would I achieve this? Keep in mind please that datasets 1 and 2 are huge - about 39 million rows.

Much appreciated.

[abhijitnalekar]

Hi @QFanatic ,

Is the Employenumber commonly filed in the three tables?

[QFanatic]

Yes the employee number field exists in all three tables.

[abhijitnalekar · Accepted solution]

Hi @QFanatic ,

Please try the below script. Please change the field and table name as per your data.

tempdataset1:
mapping load
employee,
'YES' as value
resident dataset1;

tempdataset2:
mapping load
employee,
'YES' as value
resident dataset2;

tab1:
load
employee,
applymap('tempdataset1',employee,'NO') as 'Exists in data set 1',
applymap('tempdataset2',employee,'NO') as 'Exists in data set 2';

drop Tables tempdataset1,tempdataset2

[QFanatic]

Thank you very much