Joining two file

[ravi13]

Dear knime enthusiastic,

I am facing following issue, please help me to resolve:

Input
File-1

Role ID	Role Name	Option ID
R8.1	Branch Maker	AAE
R8.1	Branch Maker	AB
R8.1	Branch Maker	ABE
R1.1	HR Payroll Maker	YFA
R1.1	HR Payroll Maker	YTH
R1.2	HR Operations Maker	AAE
R1.2	HR Operations Maker	AAH
R1.3	HR Recuritment Enquiry	C
R1.3	HR Recuritment Enquiry	YFA
R1.4	HR Operations Checker	AAE
R1.4	HR Operations Checker	AB

File-2

Role Name	Roles Assigned
RDFGE	R1.1
EDFGT	R1.1,R1.2,R1.3
GTDJE	R1.3
HJHDH	R1.4
HGFRT	R1.2

Required Output:

Role Name	Role Name	Option ID
RDFGE	HR Payroll Maker	YFA
	HR Payroll Maker	YTH
EDFGT	HR Payroll Maker	YFA
	HR Payroll Maker	YTH
	HR Operations Maker	AAE
	HR Operations Maker	AAH
	HR Recuritment Enquiry	C
	HR Recuritment Enquiry	YFA
GTDJE	HR Recuritment Enquiry	C
	HR Recuritment Enquiry	YFA

Doubt:

Here I want to first join file -1 with file-2 by assigning Option Id & Role Name against Role assigned to Role Name. I am not getting idea how to do it.

Thanks in advance

[victor_palacios]

Looks like you need the joiner node:

- YouTube

One issue, you don’t seem to have a unique key, so your joins may not be as expected.

[ravi13]

thanks,
i know that
but please go through the problem what i had explained.

[eamendola]

Hi @ravi13
I think that this is what you are looking for.
The key here is to split the Roles Assigned column since it has multiple options. After the splitting as a list, we ungroup again that list so it can became one row per role, and only then apply the joiner.

First step: Split by “,” and return as list

Second step: Ungroup that new created list

Third step: Join Table 1 with Table 2 by Role Id

After that you can rename and resort your columns and rows so you can have the desired output, something like this:

Let me know if this helps you.

[ravi13]

Thanks @eamendola
My doubt is resolved using this steps.
Tysm