04-10-2018 09:48 AM - edited 04-10-2018 09:51 AM
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|
04-10-2018 01:00 PM
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.
04-10-2018 01:08 PM
04-10-2018 05:59 PM
04-11-2018 09:40 AM
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).