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

 

Eleven 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
Eleven 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?

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

Re: How to implement multiple case statement in Tmap

did this help?
Regards
Abhishek KUMAR
Six Stars

Re: How to implement multiple case statement in Tmap

not resolved
Highlighted
Nine Stars

Re: How to implement multiple case statement in Tmap

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 @akumar2301 '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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch