topic [resolved] tmap join and treplace in Talend Studio

[resolved] tmap join and treplace

Anonymous — Wed, 02 Jul 2014 17:57:55 GMT

I have two questions.
1- I connect to sql server and i have a table with two column where if a value is set to null, I join table with second column otherwise I join with first column.
ex. first column - second column
1 X null join with first column
2 XX null join with first column
3 null Y join with second column
4 XXX null join with first column
2- I want substitute the table cell that contain a value in another value but i want substitute entire cell and not only match.
ex. column substitute bye in 1 and all other in 2 ----> column
1 XXbye 1
2 other 2
3 YbyeX 1
4 null 2

Re: [resolved] tmap join and treplace

Anonymous — Thu, 03 Jul 2014 13:13:05 GMT

Can you pl show your existing job design?
Vaibhav

Re: [resolved] tmap join and treplace

Anonymous — Thu, 03 Jul 2014 14:20:22 GMT

Can you pl show your existing job design?
Vaibhav

These are the screenshots:
in treplace i want substitute all cell table value and not only match
in tmap join with uri_b2c when value is not null otherwise join with uri_b2b. In the output I write product_id
in treplace the column is referer and not product_id, sorry

Re: [resolved] tmap join and treplace

Anonymous — Thu, 03 Jul 2014 14:52:57 GMT

If you enable Advance mode in tReplace, you could do more complex processing... try that
Vaibhav

Re: [resolved] tmap join and treplace

Anonymous — Thu, 03 Jul 2014 15:08:59 GMT

If you enable Advance mode in tReplace, you could do more complex processing... try that
Vaibhav

I have already tried it, is to replace only the string that matches. I want substitute all table cell.
Ex.
www.facebook.com matches with facebook but result is www.1.com and not 1.
My goal is substitute all matches with facebook with 1.
Ex.
www.facebook.com -> 1
http://m.facebook.it -> 1
facebook.it/search:? -> 1

Re: [resolved] tmap join and treplace

Anonymous — Thu, 03 Jul 2014 16:23:55 GMT

hi cicas,
Answer for your second question :
There is no need to use treplace component to replace. i suggest you to do like follows:
remove component treplace_1 and tconvertType_1 from your job, instead of that two componet add tmap_2. something like this:
tgetproduct---(getting full string)-----> tmap_2---lookup (data type integer)---> tMap_1
in tmap_2 just write this statement in expression builder:
(row1.patternCheck.toLowerCase().indexOf("bye") >= 0)?1:2
then you will get as your requirement. "row1.patternCheck" is your row from table
"bye" present -----> 1
"bye" not present -----> 2
hope this will solve your second question.
Regards,
Akki

Re: [resolved] tmap join and treplace

Anonymous — Thu, 03 Jul 2014 16:26:22 GMT

just for your information "tgetproduct" what i mentioned in my flow is not a component, its a mysql component name which you have given in that snap shot.
Regards,
Akki

Re: [resolved] tmap join and treplace

Anonymous — Thu, 03 Jul 2014 18:04:19 GMT

hi cicas,
Answer for your second question :
There is no need to use treplace component to replace. i suggest you to do like follows:
remove component treplace_1 and tconvertType_1 from your job, instead of that two componet add tmap_2. something like this:
tgetproduct---(getting full string)-----> tmap_2---lookup (data type integer)---> tMap_1
in tmap_2 just write this statement in expression builder:
(row1.patternCheck.toLowerCase().indexOf("bye") >= 0)?1:2
then you will get as your requirement. "row1.patternCheck" is your row from table
"bye" present -----> 1
"bye" not present -----> 2
hope this will solve your second question.
Regards,
Akki

Thanks for answer but not works. After first row, it shows error NullPointerException.
The same for row1.referer.contains("bye")?1 row1.referer.contains("hello")?2:3)

Re: [resolved] tmap join and treplace

Anonymous — Fri, 04 Jul 2014 05:09:20 GMT

hi cicas,
You have to handle null values as well.
row1.patternCheck.equals("")?2 (row1.patternCheck.toLowerCase().indexOf("bye") >= 0)?1:2)
if null value put 2 directly or may be 3(Saparate for null). If value is there then check your conditions.
Regards,
Akki

Re: [resolved] tmap join and treplace

Anonymous — Fri, 04 Jul 2014 05:10:45 GMT

sorry... that become smile in my previous post.
row1.patternCheck.equals("")?2: (row1.patternCheck.toLowerCase().indexOf("bye") >= 0)?1:2)

Re: [resolved] tmap join and treplace

Anonymous — Fri, 04 Jul 2014 08:32:09 GMT

sorry... that become smile in my previous post.
row1.patternCheck.equals("")?2: (row1.patternCheck.toLowerCase().indexOf("bye") >= 0)?1:2)

Thank you for answer but the problem is iterate for each row.
It works only with
ex. row1.patternCheck == "NULL"?1:2 and not row1.patternCheck.equals("NULL")?1:2 or .contains or .indexOf

In these case it works only for first row and then I have a NullPointerException

Re: [resolved] tmap join and treplace

Anonymous — Fri, 04 Jul 2014 09:42:35 GMT

I solved with routine
ex. public static int refererConverter(String referer) {

if(referer == null)
return 1;
else if (referer.contains("facebook"))
return 2;
else return 3;
}

Re: [resolved] tmap join and treplace

Anonymous — Fri, 04 Jul 2014 10:06:52 GMT

hi all,

you've got a null pointer exception , so you've to manage null value.
null is NOT a String in Java so =="NULL" or equals ("NULL") could not work !
in ternary expression it could be something like :

referree == null ? 1 :
referer.contains("facebook") ? 2 :
3

I didn't test the code but it should work
Be aware that you sould test if not null before anything else because if it is null, java will try something like

null.contains("facebook") /* source of your null pointer exception : null don't have a 'contains method' */

it could be a better way to put your code in routine than directly in tMap or other component
regards
laurent

Re: [resolved] tmap join and treplace

Anonymous — Fri, 04 Jul 2014 10:51:45 GMT

hi all,

you've got a null pointer exception , so you've to manage null value.
null is NOT a String in Java so =="NULL" or equals ("NULL") could not work !
in ternary expression it could be something like :
referree == null ? 1 :
referer.contains("facebook") ? 2 :
3
I didn't test the code but it should work
Be aware that you sould test if not null before anything else because if it is null, java will try something like
null.contains("facebook") /* source of your null pointer exception : null don't have a 'contains method' */
it could be a better way to put your code in routine than directly in tMap or other component
regards
laurent

Thanks Kzone, I have another question:
How can I or join betwen string1 and (string21 or string22)?
ex. id1 string1 id2 string21 string22 result id1 id2
1 bye X null dad 1 W
2 null Y null mam 2 null
3 good W bye null 3 null
4 hello Z null hello 4 Z
My goal is join with string21 but if it is null then join with string22

Re: [resolved] tmap join and treplace

Anonymous — Fri, 04 Jul 2014 11:37:05 GMT

you cannot make a join on 2 different column in a same tmap or tjoin i think.
So do it in 2 times
something like @screenshoot
first join filter null & not null value , then map not null string21 (left outer join) , then read rejected filter row that you have store in hash component and map again on string22.
Depending on String21 , 22 value write result in a single flow.
hope it help
regards
laurent