topic Re: Tmap adding sequence number in Talend Studio

Tmap adding sequence number

Anonymous — Thu, 21 Sep 2017 14:16:27 GMT

Hi,

can i please request help on how to achive the below result using tmap

Input

A134

B235

C129

Output

A134-01

A134-02

A134-03

B235-01

B235-02

C129-01

Thanks for your help.

Re: Tmap adding sequence number

Anonymous — Thu, 21 Sep 2017 14:46:47 GMT

This one is quite straight forward. In your tmap use a tMap variable (from the section in between the input and output). In that variable put the following code....

Numeric.sequence(row.input, 1, 1)

I've used row. input to represent your input column. What this does is create a sequence for each different input.

Given it a go and let us know if it works for you

Re: Tmap adding sequence number

Anonymous — Thu, 21 Sep 2017 16:13:37 GMT

thanks, sorry i am new to talend, is it possible to give step by step instruction or screenshot of tmap configuration, i get this error

Re: Tmap adding sequence number

TRF — Thu, 21 Sep 2017 17:19:11 GMT

Hi,

The error is probably due to the datatype of the variable which should be an integer to receive the result of Numeric.sequence.

However, as you expect to keep leading 0 on indices, keep the var datatype as String and replace the formula by this one:

("100" + Numeric.sequence(row1.input, 1, 1)).substring(2)

Here is the tMap configuration:

And here is the result:

Starting job test at 18:13 21/09/2017.

[statistics] connecting to socket on port 3834
[statistics] connected
A134-01
A134-02
A134-03
B235-01
B235-02
C129-01
[statistics] disconnected
Job test ended at 18:13 21/09/2017. [exit code=0]

Hope this helps.

Re: Tmap adding sequence number

Anonymous — Fri, 22 Sep 2017 10:35:15 GMT

Perfect, thanks worked like charm.

Re: Tmap adding sequence number

Anonymous — Fri, 22 Sep 2017 11:35:57 GMT

one small problem how do we deal with result that go beyond sequence 9

for example below

right now i am getting result as below.

A134-01

A134-02

A134-03

A134-04

A134-05

A134-06

A134-07

A134-08

A134-09

A134-010

A134-011

and i want output after 9 should be like

A134-10

A134-11

instead of

A134-010

A134-011

Thnaks for yoru help.

Re: Tmap adding sequence number

Anonymous — Fri, 22 Sep 2017 12:32:09 GMT

What happens if the number gets to 3 digits? I take it you don't want it truncated?

This method will help. Create a routine and add this method to the routine. Then you can use it in place of the code you are currently using.

   public static String returnZeroPrePaddedNumber(int length, String number){
    	String returnVal = number;
    	
    	while(returnVal!=null&&returnVal.length()<length){
    		returnVal = "0"+returnVal; 
    	}
    	
    	return returnVal;
    }

As an example of how to use it, at the moment you are using this.....

("100" + Numeric.sequence(row1.input, 1, 1)).substring(2)

If you create a routine called "MyUtils", and add the method I gave you to it, you would use this code....

routines.MyUtils.returnZeroPrePaddedNumber(2, ("" + Numeric.sequence(row1.input, 1, 1)))

This code takes an integer parameter which tells it how many digits are required as a minimum. The second parameter is a number as a String. If that number is less than the minimum number of digits, it adds 0s to the beginning until the minimum is reached. If the minimum is matched by the number (or the number has more digits), nothing happens to it.

Re: Tmap adding sequence number

TRF — Fri, 22 Sep 2017 13:30:23 GMT

Or just replace the expression by this one:

(""+(100 + Numeric.sequence(row1.input, 1, 1))).substring(1)

And if you want 3 leading 0, replace 100 by 1000 and so on.

Re: Tmap adding sequence number

Anonymous — Fri, 22 Sep 2017 13:57:37 GMT

thank you all for help.

Re: Tmap adding sequence number

Anonymous — Fri, 22 Sep 2017 14:03:49 GMT

That doesn't really work @TRF. That solution requires a different expression depending on the length of the number returned by the sequence.

Re: Tmap adding sequence number

TRF — Fri, 22 Sep 2017 14:14:14 GMT

@rhall, you're right, you have to know the max expected length to decide if you need to add 10, 100, 1000 and so on.

From my point of view, this is the same when you use MyUtils.returnZeroPrePaddedNumber as you must decide how many leading 0 are expected.

The advantage of this routine is that there is no truncation when the input value is too large (but in that case the result is not exactly what is expected as soon as a fixed length is required).

Re: Tmap adding sequence number

Anonymous — Fri, 22 Sep 2017 15:10:37 GMT

There is no perfect solution @TRF given the information we have. I just tried to make it a bit more flexible by setting a minimum length. The assumption being that numbers will not need to be truncated, but that you may always want at least 2 digits, for example.