Assigning ID's to column name

Five Stars

Assigning ID's to column name

Hello Team,
We have a situation where we have multiple columns say Employee Id , Employee Name,Department,Vertical,Floor etc.

What we want that that instead of Department,Vertical,Floor etc. we have id's for the same and the details of these fields will in a different table.

Through which component we can assign id's to Department,Vertical,Floor etc.?

For example

1 , Test1 , HR , BFS , 2nd Floor

2 , Test2 , Finance , Healthcare , 3rd Floor

We want like


1 , Test1 , 1 , 1 , 2

2 , Test2 , 2 , 3 , 3

and different tables for the 3 fields .For reference as below :

Department Id Department Name

1 BFS
2 Finance

etc.

 

 

Thanks,

Arvind Jha


Accepted Solutions
Fifteen Stars TRF
Fifteen Stars

Re: Assigning ID's to column name

@ArvindJha, does this helps?


TRF

All Replies
Forteen Stars

Re: Assigning ID's to column name

You can use tMap JOIN lookup

You can use table for lookup, or fixed list if it stable (think not, so table better)

-----------
Fifteen Stars TRF
Fifteen Stars

Re: Assigning ID's to column name

Hi,

Here is my proposal for your case:

Capture.PNG

 

I replace CSV file by tFixedFlowInput for the test with fixed values.

Here is the configuration for this component:

Capture.PNG

1st step, I used a tMap to separate the values for fields Department, Vertical and Floor:

Capture.PNG

After the values has been separated, I get unique values using the tUniqRow and then and a sequence number for each values of each field and the result is stored into 3 separated tables (tHashOutput). Here is the first tMap, other components for this step are quite simples:

 Capture.PNG

Be careful for the 2 others tMap to change the sequence name (for example use "v" for Vertical and "f" for "Floor").

Now we got our 3 separated tables, so we can start with the 2nd subjob to replace names by id for the 3 fields.

For that I use again the same tFixedFlowInput as for 1st subjob (change for a delimited file if you prefer).

The content goes to tMap as the main flow and the 3 tables (tHashInput) are used as lookups.

Here is the tMap with 3 inner join based on the names to get the ids:

Capture.PNG

You just have the 1st inner join on the capture but the 2 others are the same (except they use Vertical and Floor field instead of Department).

Be careful to declare all ids as integer (as they come from the sequences).

Finally, the result goes to the console using tLogRow:

Capture.PNG

Hope this helps.


TRF
Fifteen Stars TRF
Fifteen Stars

Re: Assigning ID's to column name

@ArvindJha, does this helps?


TRF

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

APIs for Dummies

View this on-demand webinar about APIs....

Watch Now