Five Stars

## Tmap, map values by a level and order it numeric

Hello, I have an tMysqlinput with the following values:

 name level level3 level4 level5 John 3 John null null Mark 4 null Mark null Maria 5 null null Maria Alex 5 null null Alex Dan 4 null Dan null Andrew 3 Andrew null null Dex 5 null null Dex Dex3 4 null dex

I need to map them like this : level3-level4-level5 :John - Mark - Maria & John - Mark - Alex .I don't have an ideea how to do this in talend.

Sixteen Stars

## Re: Tmap, map values by a level and order it numeric

There is a difficult way of doing this. I put this tutorial together a while ago to see if I could (https://www.rilhia.com/tutorials/talend-connect-example). It emulates the Oracle Connect By functionality. However, I suspect you can make it easier than that. Do you have a total number of levels? In your example, you give 5 (the assumption being 1 to 5). If that is the case, you can do it using tHashInput/Output components and a tMap.

Filter your different levels into different tHash components. Then connect them to your tMap. Then simply join the different inputs using level+1 as the key. So level 1 is joined to level 2 using 1+1 in the join, etc. You will need to use left outer joins and will probably need some filtering after the output (a tAggregateRow to remove row multiplication where it is not required). That should do it for you.

Five Stars

## Re: Tmap, map values by a level and order it numeric

Hello , Thank you for your suggestion, when i add +1, because level it's a string it will end up 31 not 4. How can I solve this? Thank you

Sixteen Stars

## Re: Tmap, map values by a level and order it numeric

Something like this might work for you....

`(Integer.valueOf(row.level)+1)+""`
Five Stars

## Re: Tmap, map values by a level and order it numeric

Thank you for your effort, but this solution is not god. I have to map level3 - level4 - level5, the problem is if the level 3 is null and I have values on level 4 or 5, i need to map the previos level1  with the new level 4 or 5 .

 name level level3 level4 level5 John 3 John null null Mark 4 null Mark null Maria 5 null null Maria Alex 5 null null Alex Dan 4 null Dan null Andrew 3 Andrew null null Dex 5 null null Dex Dex3 4 null dex

Result will be John - Mark - Maria and John - Maria - ALex and John - Dan

Sixteen Stars

## Re: Tmap, map values by a level and order it numeric

Am I to understand that order of the data is important here? For example, if the data arrives like below.....

 name level level3 level4 level5 John 3 John null null Mark 4 null Mark null Maria 5 null null Maria Alex 5 null null Alex Dan 4 null Dan null Andrew 3 Andrew null null Dex 5 null null Dex Dex3 4 null dex null

....would the expected output be as below.....

John - Mark - Maria

John - Mark - Alex

Dan

Andrew - Dex

Dex3

If so, I can see the logic. If not, you need to explain the logic in more detail. Unless order of rows dictates how the hierarchy is mapped (ie whether the levels relate to each other), I am not sure how it works.

Five Stars

## Re: Tmap, map values by a level and order it numeric

Yes the order is numeric 3 is related to 4 and 5 also just 3 and 4 if 5 is null or empty.

 name level level3 level4 level5 John 3 John null null Mark 4 null Mark null Maria 5 null null Maria Alex 5 null null Alex Dan 4 null Dan null Andrew 3 Andrew null null Dex 5 null null Dex Dex3 4 null dex null

Level 3 is allways the top level so we will have

John - Mark - Maria

John - Mark - Alex

John - Dan

Andrew - Dex

Andrew - Dex3

Sixteen Stars

## Re: Tmap, map values by a level and order it numeric

So whenever level 3 is in a row, it restarts the chain? If that is the case, I can knock up an example later (am a bit busy with my current client at the moment)

Five Stars

## Re: Tmap, map values by a level and order it numeric

yes, because 3 si the first level. Thank you.

Sixteen Stars

## Re: Tmap, map values by a level and order it numeric

I believe I have solved this. It was actually harder than I expected, but I like a challenge. I had to make some assumptions and added some other scenarios to the ones you gave. Essentially the only hard rule I had for this was that all groups MUST start with a level 3. You can have ...

1) Level 3 on its own

2) Level 3 followed by Level 4 only

3) Level 3 followed by Level 4 and Level 5

4) Level 3 followed by Level 5

5) If Level 3 is followed by 2 Level 4s and then a Level 5, the Level 3 and first Level 4 will be one group, and the Level 3, second Level 4 and Level 5 will be the second group

I hope this logic is correct. I have included a copy of the job I created. The logic is too convoluted to explain on here without writing an essay. You should be able to simply replace my tFixedFlowInput with your data source.

Let me know how you get on. The job was written in 6.4.1. I am hoping you are using that version or newer or you will not be able to open it.