"if then else" in derivation
Moderators: chulett, rschirm, roy
"if then else" in derivation
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
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
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.
==========
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.
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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:
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.
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
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
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
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
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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:
Define a single key column in the hash file, and set its derivation to:
Set the constraint of the output link to:
This job will create 8 rows in the hash file.
Create a stage variable named i, and set its initial value to 0. Set its derivation to:
Code: Select all
i + 1
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'
Code: Select all
i <= 8
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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]
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
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Another possible solution:
Regards,
Burkard
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
Burkard