Which components use to add sequence numbers to columns and then normalize the table?

[Anonymous]

Hi everyone, first of all sorry for my English, i'm starting using talend and I need your help please.
I would like to add an id column to each column in the same excel file and in the same time (or after) normalize the repeating values.
I have a data set as below

Region Department locality zipcode
A. H. T. 01
A. H. M. 01
A. J. N 02
B. G. D. 07
B. I. Z. 08
B. I. E. 09
C. P. R. 15
C. F. T. 16
C. W. Y. 17

The locality column is the only one with no repeating values. The goal is to transform this table in order to have an id column with unique values for each repeating attributes.
So in the first step I could make an output containing ids for each column using tmap and tfileoutputexcel components but concerning the normalization I failed because I still get repeating values using tnormalize. Maybe my procedure is not the good one. Please can you give me a hint.

[TRF]

No component for that.
Just add an Integer field to your schema and populate it using Numeric.sequence.
For example, Numeric.sequence("s1", 1)

[Anonymous]

Thanks for your answer. I get this point, I did as you explained I added an integer field in the field of tmap but where I have a real issue it is in the second part of my transformation I mean the normalization. I'm struggling to normalize all the table in such way the ids will not be different for the same repeating attribut.

[Anonymous]

Hi,

      You can use the below pattern to aggregate the values and add unique sequence number to the records.

You can modify the list of columns in the taggregate component based on your need. I have added the schema as below.

In the tmap component, you can add a numeric sequence to store the ids for each record.

If the reply has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

[Anonymous]

.

[Anonymous]

Hi, actually the purpose of my work is to load data into a data warehouse so in this step what I am looking for it is to add column id to my table firstly and then normalize the table in order to eliminate redondant data.

To achieve the first part, I want to add numeric sequence based on value in the data. the input file looks like this:

Label

CAR

CAR

CAR

BIKE

BIKE

WHEEL

WHEEL

WHEEL

My desire output with id column should be like this

Label       ID

CAR         1

CAR         1

CAR         1

BIKE        2

BIKE        2

WHEEL   3

WHEEL   3

WHEEL    3

In my research I found a kind of answer that can help me but I don't know how to modify the code to meet my requirement. This is the code: Numeric.sequence(String.valueOf(row4.Ib_sharepoint),1,1)

In addition to increment the id number, this code also reset the id number at each new label value so that the output looks like this:

Label       ID

CAR         1

CAR         2

CAR         3

BIKE        1

BIKE        2

WHEEL   1

WHEEL   2

WHEEL    3

As you can see it's not too far of what I am looking for. So now I don't know how to move on please can anybody help me with this code or maybe give me another way to do it.

Regards.

[Anonymous · Accepted solution]

Hi,

     Could you please try following solution? Its yielding correct results.

The taggregaterow component needs to be configured as show below.

tmap_1 settings are as shown below.

tmap_2 settings are as shown below.

If the solution given has helped you, could you please mark the topic as resolved? Kudos are also welcome

Warm Regards,

Nikhil Thampi

[Anonymous]

Thank you guys, It works like a charm.