How to implement multiple case statement in Tmap

Six Stars

How to implement multiple case statement in Tmap

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

 

Six Stars

Re: How to implement multiple case statement in Tmap

Six Stars

Re: How to implement multiple case statement in Tmap

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

Re: How to implement multiple case statement in Tmap

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

 

also StringHandling routine has function for instr and substr

Regards
Abhishek KUMAR
Six Stars

how to find the greaest between two dates

Gurus,

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

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

Thanks

 

Six Stars

Re: How to implement multiple case statement in Tmap

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

Six Stars

Re: How to implement multiple case statement in Tmap

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 ?
Regards
Abhishek KUMAR
Six Stars

Re: How to implement multiple case statement in Tmap

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,
Six Stars

Re: How to implement multiple case statement in Tmap

it should quite easy to do this with tExtractRegexField component .

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

it should give you 1st field.
Regards
Abhishek KUMAR
Six Stars

Re: How to implement multiple case statement in Tmap

TalendSolution_4.JPG

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

Regards
Abhishek KUMAR

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.