topic Re: Tmap, map values by a level and order it numeric in Talend Studio

Tmap, map values by a level and order it numeric

Anonymous — Sat, 16 Nov 2024 08:48:21 GMT

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.

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

Anonymous — Thu, 25 Jan 2018 16:04:24 GMT

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.

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

Anonymous — Fri, 26 Jan 2018 09:11:32 GMT

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

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

Anonymous — Fri, 26 Jan 2018 09:16:38 GMT

Something like this might work for you....

(Integer.valueOf(row.level)+1)+""

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

Anonymous — Fri, 26 Jan 2018 12:12:32 GMT

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

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

Anonymous — Fri, 26 Jan 2018 13:45:30 GMT

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.

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

Anonymous — Fri, 26 Jan 2018 14:23:58 GMT

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

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

Anonymous — Fri, 26 Jan 2018 14:29:55 GMT

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)

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

Anonymous — Fri, 26 Jan 2018 14:32:42 GMT

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

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

Anonymous — Fri, 26 Jan 2018 21:27:43 GMT

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.

DemoLevelJob.zip