"if then else" in derivation

[evanmaas]

Hi,

Is there another solution (function, routine, transform, ...) for a check of a range values like this.

If (InterfaceData.BRANCHECODE = '260' Or InterfaceData.BRANCHECODE = '266' Or InterfaceData.BRANCHECODE = '270' Or InterfaceData.BRANCHECODE = '271' Or InterfaceData.BRANCHECODE = '272' Or InterfaceData.BRANCHECODE = '273' Or InterfaceData.BRANCHECODE = '280' Or InterfaceData.BRANCHECODE = '295' ) And InterfaceData.BOEKMAAND >= '200108' And InterfaceData.MUTATIECODE = '040' Then '044' Else InterfaceData.MUTATIECODE

This is code used in a derivation.

Regards,

Erik

[ANSHULA]

write routine something like this :
==========
var1 = BRANCHECODE
var2 = BOEKMAAND
var3 = MUTATIECODE

If (var1 = 1 or var1 = 2) and var2 >= 0 and var3 = 1 then
Ans = var3
end
else
Ans = 0
end
=========
where Routine Arguments are BRANCHECODE, BOEKMAAND and MUTATIECODE
=========

U can modify this routine code according to your business rules. and then, this routine can be used in the column derivation part of transformer stage.

[chucksmith]

This code will run faster as a derivation. You may want to use a transform function with the same three arguments described in the previous response. A slightly quicker implementation may be:

Code: Select all

If (InterfaceData.BRANCHECODE = '260' Or InterfaceData.BRANCHECODE = '266' Or (InterfaceData.BRANCHECODE >= '270' And InterfaceData.BRANCHECODE <= '273') Or InterfaceData.BRANCHECODE = '280' Or InterfaceData.BRANCHECODE = '295' ) And InterfaceData.BOEKMAAND >= '200108' And InterfaceData.MUTATIECODE = '040' Then '044' Else InterfaceData.MUTATIECODE 

If you decide to use a routine, I would use nested If statements, and organize them so that the routine will return as fast as possible.

[tonystark622]

Another way to solve this might be to create a flat file with all the valid values ('260', '266', '270', '271', '272', '273', '280', '295' ). Read the flat file into a hash file and do a lookup on BRANCHECODE.

Your logic becomes

If (Not(IsNull(BranchCodeLU.BrancheCode)) ) And InterfaceData.BOEKMAAND >= '200108' And InterfaceData.MUTATIECODE = '040' Then '044' Else InterfaceData.MUTATIECODE

It reads a lot cleaner anyway.

Hope this helps,
Tony

[chucksmith]

You can skip the flat file. Use a transform with an output to a hash file, and set the clear file before writing flag.

Create a stage variable named i, and set its initial value to 0. Set its derivation to:

Code: Select all

i + 1

Define a single key column in the hash file, and set its derivation to:

Code: Select all

If i = 1 Then '260' else if i = 2 then '266' else if i = 1 then '270' else if i = 4 then '271' else if i = 5 then '272' else if i = 6 then '273' else if i = 7 then '280' else '295'

Set the constraint of the output link to:

Code: Select all

i <= 8

This job will create 8 rows in the hash file.

[vmcburney]

All of the answers will work. An important question is whether this rule will change over time, will codes be added or removed? If you are expecting changes then maintaining the BRANCHECODE values in a database table or flat file and then loading them into the routine via a hash file or sequential file commands may save time later.

Since it is an interesting piece of syntax here is my vote. I would always implement this type of logic as a routine so I can use the "Test" function to verify a full range of scenarios, reuse the rule in multiple jobs and change it without recompiling jobs. So BRANCHECODE, MUTATIECODE and BOEKMAAND are input parameters:

Code: Select all

* Save branch strings as a delimited string or dynamic array by hard coding or retrieving from a file.
BranchCompareString='260':|:'266':|:'270':|:'271'...

* Set the default return value if no criteria are met.
Ans = MUTATIECODE

* Run the business rules.
IF BOEKMAAND >= '200108' And MUTATIECODE = '040' Then
   if index(BranchCompareString, BRANCHECODE, 1) > 0 Then
       Ans = '040'
   end else
       Ans = '044'
end

[/code]

[apbr]

Another possible solution:

Code: Select all

If InterfaceData.BRANCHECODE matches Ereplace('260,266,270,271,272,273,280,295', ',', @VM) And InterfaceData.BOEKMAAND >= '200108' And InterfaceData.MUTATIECODE = '040' Then '044' Else InterfaceData.MUTATIECODE 

Regards,

Burkard

[rasi]

Hi

As Vincent said put the values in a reference table and then use it as hash file as lookup. This makes life easy and less maintenance for the developers in future.

Thanks
Rasi