topic Re: How to implement multiple case statement in Tmap in Talend Studio

How to implement multiple case statement in Tmap

AAA999 — Tue, 08 Jan 2019 16:13:38 GMT

Talend Gurus,

can anyone let me know how to handle multiple case statement in Tmap?

nvl(CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN
REPLACE(F1,' ')
ELSE substr(F1,1,regexp_instr(F1,'/|,| ')-1)
END
,F1)
as X1,

CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN NULL ELSE
REGEXP_REPLACE(SUBSTR(F1, regexp_instr(F1,'/|,| ')),'/|,|')
END AS X2,

(CASE WHEN INSTR (F1,'@',1,2) = 0
OR F1 LIKE '%<%' OR F1 LIKE '%>%' OR UPPER(F1) LIKE '%IN%' OR LENGTH(F1) >= 10
THEN 'Y' ELSE 'N' END) AS Y,

(CASE WHEN INSTR (F1,'@',1,2) = 0 THEN 'A'
WHEN F1 LIKE '%<%' OR F1 LIKE '%>%' THEN 'C'
WHEN UPPER(F1) LIKE '%IN%' THEN 'CI'
WHEN LENGTH(F1) >= 10 THEN 'L'
END) AS Z

Thanks

Re: How to implement multiple case statement in Tmap

akumar2301 — Tue, 08 Jan 2019 16:19:40 GMT

https://community.talend.com/t5/Design-and-Development/Using-local-variable-in-tmap-expression-builder/m-p/136652#M85008

Re: How to implement multiple case statement in Tmap

AAA999 — Tue, 08 Jan 2019 16:28:47 GMT

I have multiple substr and instr also in my case statement. I wrote the code in oracle, how to write in talend

Re: How to implement multiple case statement in Tmap

akumar2301 — Tue, 08 Jan 2019 16:31:15 GMT

https://help.talend.com/reader/VXLHZvns6nwhPGeQbm1Iig/2PD0dBEfYwKBhZpu3o4vMA

also StringHandling routine has function for instr and substr

Re: How to implement multiple case statement in Tmap

AAA999 — Thu, 10 Jan 2019 18:25:11 GMT

Gurus,

Can anyone let me know what is the function in talend for comparing 2 dates?

greatest (dt1, nvl(dt2,dt1)) as date.

Thanks

Re: How to implement multiple case statement in Tmap

AAA999 — Mon, 14 Jan 2019 14:29:58 GMT

Still not resolved. is there any function available rather than writing a routine for this solution?

Re: How to implement multiple case statement in Tmap

akumar2301 — Mon, 14 Jan 2019 17:41:25 GMT

Can you please write your user story ? It’s complicated to read sql
statements.

Which functionality you are not able to achieve w/o user defined routines ?

Re: How to implement multiple case statement in Tmap

AAA999 — Mon, 14 Jan 2019 18:21:07 GMT

I am trying to achieve this using tmap. I have multiple case statement with substring, instring, regular expressions(Regexp_Instr, regexp_replace, Rexexp_like) inside the case statement. I couldn't find a function that can be used to achieved using the rule defined. I am not sure why one needs to write a routine to achieve it.

If the source data has delimiters like ,'/|,| -' then replace it with space.
and capture the value before the space
tom ; tim
john , jack
jill/// john
sandy - june
linda

result
tom
john
jill
sandy
linda

nvl(CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN REPLACE(F1,' ')
ELSE substr(F1,1,regexp_instr(F1,'/|,| ')-1)
END
,F1)
CASE WHEN regexp_instr(F1,'/|,| ') = 0 THEN NULL ELSE
REGEXP_REPLACE(SUBSTR(F1, regexp_instr(F1,'/|,| ')),'/|,|')
END AS X2,

Re: How to implement multiple case statement in Tmap

akumar2301 — Mon, 14 Jan 2019 20:11:55 GMT

it should quite easy to do this with tExtractRegexField component .

Try with regex "([a-zA-Z]+)(.*)"

it should give you 1st field.

Re: How to implement multiple case statement in Tmap

akumar2301 — Wed, 16 Jan 2019 20:42:41 GMT

It works . Please try. you will get your expected result.

Re: How to implement multiple case statement in Tmap

akumar2301 — Mon, 21 Jan 2019 17:52:42 GMT

did this help?

Re: How to implement multiple case statement in Tmap

AAA999 — Tue, 22 Jan 2019 20:33:38 GMT

not resolved

Re: How to implement multiple case statement in Tmap

ankit7359 — Wed, 23 Jan 2019 06:59:38 GMT

Hi @AAA999 ,

As per your scenario, i have a query though will the delimiter option be the same, will it always consist of ", or / or ; etc"???

One solution what you can implement as per your sample data is -

you can create a variable in tmap and give the logic as if the row consists any of the specified delimiter then it will give the specified result.(if you dont want to use user-defined routines)

else you can go with @uganesh 's solution of textractregexfield or you can try textractdelimitedfields.

Logic -

row1.column1.consists(";")?TalendStringHandling.Left(row1.column1,TalendStringHandling.indexof(";"))

:row1.column1.consists("/")?TalendStringHandling.Left(row1.column1,TalendStringHandling.indexof("/"))

:etc.

You can use substring function as well, if you dont want to use in-build Stringhandling functions.

Pls do share your job designs and results if you have any issues.

Pls get back to Community, if necessary.

Thanks,

Ankit.