SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Mapping in DI Studio

Reply
Super Contributor
Posts: 625

Mapping in DI Studio

[ Edited ]

Appreciate if someone of you help me with the best approach to create the following fields. I believe I can use extract transformation but I've to use four extract transformation(one for each variable/field) . Is there any other methods available to accomplish this?

 

Please note that I need to use two different datasets (F_CL_CLAIM  and CLAIM) to create the following variables.

 

Input Table Input Field Mapping Condition Output Field
F_CL_CLAIM  amt CLAIM.CLAIM_AMT_TYPE_CD='2'  and SUBTYPE_CD='2' NVK
F_CL_CLAIM  amt CLAIM.CLAIM_AMT_TYPE_CD='2'  and SUBTYPE_CD='1' SRK
F_CL_CLAIM  amt CLAIM.CLAIM_AMT_TYPE_CD='3'  and SUBTYPE_CD='1' SNK
F_CL_CLAIM  amt CLAIM.CLAIM_AMT_TYPE_CD='2'  and SUBTYPE_CD='3' TRK
Super User
Posts: 5,849

Re: Mapping in DI Studio

If you need two tables extract is not the transformation to use. 

Sounds like a typical SQL Join scenario.

And you don't need a separate transformation for each column calculation...? Just define the case logic for each column in the transformation.

Data never sleeps
Super Contributor
Posts: 625

Re: Mapping in DI Studio

Yes, I no need to use separate transformation for each column. I need to
create only one table which has all those four columns.

Could you please help me understand how I can use case when logic or join
transformation here?
Super User
Posts: 5,849

Re: Mapping in DI Studio

Is right there in the mappings.
You could either so the full point-and-click which is a bit cumbersome, or just write the syntax as services mapping.
See doc and/or help pages within the application for details.
Data never sleeps
Super Contributor
Posts: 625

Re: Mapping in DI Studio

Are you suggesting me to use 'case when' in expression tab? If not, could
you please point me to the documentation which you wanted to convey?
Occasional Contributor
Posts: 14

Re: Mapping in DI Studio

You can also try this code in the expression field (functions are usually most efficient):

 

IFN(CLAIM.CLAIM_AMT_TYPE_CD = '2' AND CLAIM.SUBTYPE_CD = '2', F_CL_CLAIM.AMT, 0)

 

This IFN function returns the result (AMT) if condition is true, otherwise returns 0 (or any other value you want). Remember to map all input fields to the expression (using the SQL Join).

Ask a Question
Discussion stats
  • 5 replies
  • 127 views
  • 1 like
  • 3 in conversation