Six Stars

tNormalize with index or sorting number

-- Info: I noticed that I added this to the wrong board, but it seems I can neither delete this post, nor move it to another board - for whatever reason. Sorry. Could an admin please move this to the correct board? --

Hi Community-members!

 

I have a very small problem with the tNormalize component, I was not yet able to resolve.

 

Is there a way to provide some kind of index number of the splitted elements, that tNormalize turns into rows?

 

e.g.
Input:
key | string
01 | value1,value2,value3
02 | value1,value2,value3,value4
...

Output:
key | index | value
01 | 1 | value1
01 | 2 | value2
01 | 3 | value3
02 | 1 | value1
02 | 2 | value2
02 | 3 | value3
02 | 4 | value4

Currently I have added a tMap component, that adds a sequence-number to the generated rows, but for some reason, the number skips from time to time (e.g. from row 81 to row 82, the index jumps from 81 to 382) and I am not able to see what the problem is. And in addition: this would add a number over all records, not in a "window-behavior" reset the number for each "block".

Any ideas? Has anyone else had the same problem?

Thanks in advance!

Freddy

1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

Re: tNormalize with index or sorting number

Hi,

 

If key is unique then you can use key value to generate index value in tMap.

 

try this : in tMap create variable port index= Numeric.sequence(Key,1,1)

 

For every key value it will generate sequence.

 

key | index | value
01 | 1 | value1
01 | 2 | value2
01 | 3 | value3
02 | 1 | value1
02 | 2 | value2
02 | 3 | value3
02 | 4 | value4

 

Regards,

Veeru Boppudi
3 REPLIES
Nine Stars

Re: tNormalize with index or sorting number

Hi,

 

If key is unique then you can use key value to generate index value in tMap.

 

try this : in tMap create variable port index= Numeric.sequence(Key,1,1)

 

For every key value it will generate sequence.

 

key | index | value
01 | 1 | value1
01 | 2 | value2
01 | 3 | value3
02 | 1 | value1
02 | 2 | value2
02 | 3 | value3
02 | 4 | value4

 

Regards,

Veeru Boppudi
Six Stars

Re: tNormalize with index or sorting number

It works thanks for showing that, Initially I did not get it to work as I was getting index from 1 to XX but then I had not appened my tmap row. to the key

Thanks for sharing
Six Stars

Re: tNormalize with index or sorting number

Veeru, you are my hero! This is brilliant. Never thought of using the key variable itself as the sequence identifier.

 

Thank you very much, it works like a charm.

 

Here is the complete workflow, if someone needs a working example:

workflow_1.PNG

workflow_2.PNG