[resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

Highlighted
One Star

[resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

Hi, i'm trying to replace the string values of my table using tmap, using talend Numeric operations, but one register in the id field is numeric (actually is zero). For example:

id | name  --> id | name
A | thing  --> 1 | thing
B | thing  --> 2 | thing
C | thing  --> 3 | thing
D | thing  --> 4 | thing
0 | thing  --> 5 | thing

Thanks


Accepted Solutions
Ten Stars

Re: [resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

Hang on, what exactly are you trying to do here? I see two possibilities from what you have written. The first is that you want each id value to be translated to a number. This would result in the below....

ID|New value
A | 1
B | 2
C | 3
D | 4
0 | 5
A | 1
B | 2
D | 4

The second is that you want a count of each value, which is what your usage of Numeric.sequence will achieve. It would look like this...

ID|New value
A | 1
B | 1
C | 1
D | 1
0 | 1
A | 2
B | 2
D | 2
D | 3

If it is the first way, you can simply use a find and replace in your tMap if you know all of the possible values, alternatively you could maybe be clever about it and use the character's char value and a bit of maths (the char value of A is 65, B = 66, etc; 65-64 = 1, 66-64 = 2, etc).

If it is the second way, the Numeric.sequence which I corrected will do.

View solution in original post


All Replies
One Star ark
One Star

Re: [resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

Hello Yuryja,

Is it possible to get the data into the tMap component in the order you want them to be in? If so, you could define a sequence (using Numeric.sequence Talend function) in the Var column and set the new variable to fill the ID column you desire. This will fill the ID column with numbers from 1 and it will increase the variable by a step you define in the function.

Regards,
Matej
One Star

Re: [resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

Hi ark,
I used Numeric.secuence like this:
"Numeric.sequence(row1.id_cat_cli,1,1)"
where id_cat_cli has the A,B,C,D,0 (zero) secuence and i got the following answer:
    "The method sequence(String, int, int) in the type Numeric is not applicable for the arguments (int, int, int)"

How can i fix the problem without updating ids directly on the origin table by SQL commands?

Thanks!
Ten Stars

Re: [resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

"row1.id_cat_cli" is an int. It needs to be a String to work with the Numeric.sequence method. Try this....

Numeric.sequence(row1.id_cat_cli+"",1,1)
One Star ark
One Star

Re: [resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

The first parameter in the Numeric.sequence function is a name of the sequence. By using the dynamic name of the sequence the result will be duplicate ids, because every distinct value from the column ID from input, will have it's own sequence. The name should be String constant, probably hard-coded. Just so the function can distinguish the sequence and assign a new incremented value.
Ten Stars

Re: [resolved] Replacing A,B,C,D,0 for 1,2,3,4,5 id autoincrement

Hang on, what exactly are you trying to do here? I see two possibilities from what you have written. The first is that you want each id value to be translated to a number. This would result in the below....

ID|New value
A | 1
B | 2
C | 3
D | 4
0 | 5
A | 1
B | 2
D | 4

The second is that you want a count of each value, which is what your usage of Numeric.sequence will achieve. It would look like this...

ID|New value
A | 1
B | 1
C | 1
D | 1
0 | 1
A | 2
B | 2
D | 2
D | 3

If it is the first way, you can simply use a find and replace in your tMap if you know all of the possible values, alternatively you could maybe be clever about it and use the character's char value and a bit of maths (the char value of A is 65, B = 66, etc; 65-64 = 1, 66-64 = 2, etc).

If it is the second way, the Numeric.sequence which I corrected will do.

View solution in original post

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 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog