Matching Skills to employee skills

[Radhika3]

I am trying to connect two tables employees and skills. The employee table and skills tables are connected via an other table which has the key. I want to create a column skilled in the cases where the skills in the task table for a task id are present in the employee tagged to the task else the employee is not skilled

[BrunPierre]

It would be useful to see a sample of the data

[Radhika3]

Hi,

The data consists of 3 tables the fact table with the EIN and the taskID. The Task table with the task Id and the skills needed for the task. The employee table with the EIN and the employee skills. Tables are as below:

The employee table is 

Task ID	EIN
1	1
2	1
3	2
4	2
5	3

The employee table 

EIN	Skill_Emp
1	A
1	B
1	C
2	A
2	B
3	D
3	E

The skill table is :

Task ID	Skill_Task
1	A
1	B
2	A
3	A
3	B
3	C
4	C
4	D
5	E

The Aim to create an other column called as skilled where if the employee has the skills required for the task then he can be considered as skilled else he is not skilled. Each employee is tagged to a task in the task table

[deepanshuSh]

You can do it multiple ways, first you can use the applymap for getting the value of the skill_task onto the 

The employee table is 

Task ID	EIN
1	1
2	1
3	2
4	2
5	3

Then you can join the table containing the new added field, with the employee table, thereby creating a final single table containing all the required fields in single table.  After that create the if and else condition for the required result. Like if (num(skill_task)=num(skill_emp), 'skilled', 'Unskilled') to create the required table. 

[Saravanan_Desingh]

Try this,

Emp:
LOAD * INLINE [
    EIN, Skill_Emp
    1, A
    1, B
    1, C
    2, A
    2, B
    3, D
    3, E
];
Left Join(Emp)
LOAD EIN, Concat(DISTINCT Skill_Emp) As EmpSkills
Resident Emp
Group By EIN;

Left Join(Emp)
Task:
LOAD * INLINE [
    Task ID, EIN
    1, 1
    2, 1
    3, 2
    4, 2
    5, 3
];

Left Join(Emp)
TaskSkill:
LOAD * INLINE [
    Task ID, Skill_Task
    1, A
    1, B
    2, A
    3, A
    3, B
    3, C
    4, C
    4, D
    5, E
];

Left Join(Emp)
LOAD [Task ID], Concat(DISTINCT Skill_Task) As TaskSkills
Resident Emp
Group By [Task ID];

Left Join(Emp)
LOAD EIN, [Task ID], If(Index(EmpSkills,TaskSkills)>0,'Y','N') As Skilled
Resident Emp;

Output:

[Radhika3]

Hey Thank you for the solution, but in the real data our skills are not single characters, skills are long and  combination of alphanumeric characters so we are getting a N everywhere

[Saravanan_Desingh]

Hello. Please post some sample.