Help with Dataprep Lookup functions

[thomaskerr5]

I have a data set that is a benefit employee census. In this data set through DataPrep, we are attempting to accomplish something that we are struggling with. We have one column that has a unique identifier established by the report system. This is assigned to an employee and the same identifier is carried to each of their dependents. An example (the cells/columns are delineated by the | marker):

Employee | Mary Smith | Unique Identifier 555666 | SSN 000-11-2222

Child | John Smith | Unique Identifier 555666 | SSN 111-22-3333

Child | Emily Smith | Unique Identifier 555666 | SSN 222-33-4444

Employee | Bill Anderson | Unique Identifier 444333 | SSN 999-88-7777

Spouse | Sandra Anderson | Unique Identifier 444333 | SSN 666-55-4444

We are wanting to get the Unique Identifier to associate with the employee's SSN instead of the generated number, and where the unique SSN's are still listed, but the employees' SSNs are also reported on their respective dependent lines, creating a data set that looks like this:

Employee | Mary Smith | Employee SSN 000-11-2222 | SSN 000-11-2222

Child | John Smith | Employee SSN 000-11-2222 | SSN 111-22-3333

Child | Emily Smith | Employee SSN 000-11-2222 | SSN 222-33-4444

Employee | Bill Anderson | Employee SSN 999-88-7777 | SSN 999-88-7777

Spouse | Sandra Anderson |Employee SSN 999-88-7777 | SSN 666-55-4444

Could someone help with the steps we would need to take to accomplish this? In Excel, I would just use an xlookup function, but I cannot find out how to accomplish here.

Thank you!

[alexnajm]

Done with RegEx Replace!

[thomaskerr5]

Hello, Thank you so much for your reply. Unfortunately I do not have access to designer cloud upon further inspection, only Dataprep itself, which Alteryx has confirmed I cannot open yxmd files in. I believe I likely posted my question in the wrong community box, but hoping you can still help. As I am very new at this process, could you provide an excel file example and describe the steps used to implement the function? So sorry for any inconvenience! I appreciate your help.

[alexnajm]

Not sure what you are referring to with “DataPrep” - Alteryx has Desktop Designer or Cloud Designer. 

[thomaskerr5]

No worries, it is Dataprep by Trifacta through Alteryx. I asked the support directly through the app the same question, and they sent me here. I will browse around for the correct community box to post in, apologies for wasted time!

[alexnajm]

Ah ok, I just know it as Trifacta Classic. I assume there's a similar Regex Replace function that you might be able to get from this screenshot. Good luck!

[AkimasaKajitani]

Regarding to the Trifacta(DataPrep), that procedure is a bit complex. 

Supposing that the input is as follows.

 

The rough procedure is as follows.

1. Make the Lookup table

2. Join the tables

1. For create the Lookup table, the recipe is as follows.

The way is add the row number group by Unique·Identifier. And then, filter the RowNumber =1. So, you can find the Lookup table.

2. Aftar procedure 1, you need to join the Lookup table to the source table. the recipe is as follows.

 

The important point is number 1 "Inner Join". Click the plus button of the recipe of Lookup table and select the "Append Join". 

Aftar that, select the Datasets in current flow tab and select the source table.

 

And then, you can join the lookup table.

Finally I got this table.

 

Please check the attached flow and source file.