Five Stars

Tmap, map values by a level and order it numeric

Hello, I have an tMysqlinput with the following values:

 

namelevel level3level4 level5
John3Johnnull null
Mark4nullMarknull
Maria5nullnullMaria
Alex5nullnullAlex
Dan4nullDannull
Andrew3Andrewnullnull
Dex5nullnullDex
Dex34nulldex 

 

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.

9 REPLIES
Forteen 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.

Rilhia Solutions
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

 

Forteen Stars

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

Something like this might work for you....

(Integer.valueOf(row.level)+1)+""
Rilhia Solutions
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 . 

 

namelevel level3level4 level5
John3Johnnull null
Mark4nullMarknull
Maria5nullnullMaria
Alex5nullnullAlex
Dan4nullDannull
Andrew3Andrewnullnull
Dex5nullnullDex
Dex34nulldex 

 

 

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

 

Forteen 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.....

 

namelevel level3level4 level5
John3Johnnull null
Mark4nullMarknull
Maria5nullnullMaria
Alex5nullnullAlex
Dan4nullDannull
Andrew3Andrewnullnull
Dex5nullnullDex
Dex34nulldex 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. 

Rilhia Solutions
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. 

namelevel level3level4 level5
John3Johnnull null
Mark4nullMarknull
Maria5nullnullMaria
Alex5nullnullAlex
Dan4nullDannull
Andrew3Andrewnullnull
Dex5nullnullDex
Dex34nulldex null

 

Level 3 is allways the top level so we will have 

John - Mark - Maria

John - Mark - Alex

John - Dan

Andrew - Dex

Andrew - Dex3

 

Forteen 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)

Rilhia Solutions
Five Stars

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

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

Forteen 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. 

 

 

Rilhia Solutions