how to use below case statement in tmap

Six Stars

how to use below case statement in tmap

Hi People,

Can somebody help me converting below case statement in tmap please.

 

case when substr(naics_cd,1,2)='23' then 'Construction'
when substr(naics_cd,1,2)='11' then 'Agriculture'
when (substr(naics_cd,1,2) in ('31','32','33') and SUBSTR(NAICS_CD,1,4) not in('3366')) then 'Manufacturing'
when (substr(naics_cd,1,4) in('3366', '4872', '4883') or substr(naics_cd,1,3)='483') then 'Maritime'
else 'General Industry'
END as col

 

Thanks in advance

Teja

Community Manager

Re: how to use below case statement in tmap

There are several ways to do this.

1) You can write a routine which uses a Java switch statement (https://docs.oracle.com/javase/tutorial/java/nutsandbolts/switch.html  and  https://www.talendbyexample.com/talend-code-routines-reference.html).

2) You can make use of tMap variables and test each of your cases per variable. For example, you could have 6 variables (Construction, Agriculture, Manufacturing, Maritime, General_Industry and Result)and test your column values against each of them. Then use the Result variable to assess which variable value to use. An example of the variable code (without the substring Java since this can be Googled) would be.....

Var.Construction

row1.value.compareToIgnoreCase("23")==0 ? "Construction" : null

Then the Result variable would hold something like below (assuming the other variables have been configured)...

Var.Construction!=null ? Var.Construction : Var.Agriculture !=null ? Var.Agriculture : Var.Manufacturing!=null ? Var.Manufacturing : Var.Maritime!= null ? Var.Maritime : Var.General_Industry!=null ? Var.General_Industry : null

 

......there are other ways, but the above should help you

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

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads