Exclude members from rule expression

[AndreaF]

Hi,

I have two sets of derivative source rules (see below). 

What I am looking for is to exclude the Accounts starting with 9203 from the second set of derivative source rules.

Each derivative source rule loops through all source data rows, so it is not like in the mask rules, where placing the 9203* expression before the 92* expression would automatically exclude the first from the second set.

What's the syntax to exclude something from a rule expression (in derivative rules)? Thank you

[RobbSalzmann]

Try using a couple interim totals:

[AndreaF]

Hi, thank you for your reply. Your suggestion wouldn't work in my case because accounts are alphanumeric, so I cannot define numeric ranges. An example of account is "921N-05B".

I was thinking maybe using a complex expression is possible to return the difference between 2 derivative rules, so having a rule row for A#[9203*], one for A#[92*] as interim rule and a Final rule that does the difference between the first 2.

Would anyone be able to help with a code example of such expression?

[JackLacava · Accepted solution]

I'm not good at Transformation rules, but I don't think you can do a difference easily - you'd need a buffer, which is only available on BiBlend derivatives. However, it might be enough to place something like this on your 92* line, to nullify any value coming from 9203:

if args.GetSource("A#").StartsWith("9203") then
   return 0
end if
return args.ColumnValue

[MarcusH]

In situations where it is difficult/impossible to reference the source members, I use another UD as a helper. Typically there is a UD that is not used for the data import and load (if there are no free UDs then enable an Attribute or Label dimension). What I do then is apply a BR on the data source for this working UD - let's say it is UD7. In your case the BR will set the value of UD7 to be 9203 for accounts that begin with 9203 and then 92 for all other accounts beginning with 92 (simple to do in a BR). Then in the Rule expression you add .U7#[9203]=None for HoldCo and .U7[92]=None for Loans. The transformation rules for UD7 map everything to the valid UD7 member.

I find that derivative rules are hard to understand in the first place. You could possibly do it by changing the Logical Operator and Derivative Type somehow but I prefer to keep the process of creating extra data records as transparent as possible.

HTH

[AndreaF]

Thank you, all of 3 are good ideas! The one I ended up implementing is Jack's solution, setting the derivative rule as complex expression and using the proposed formula. Only downside is that the rule creates zero rows that are not really needed: each [92*] row creates a row with the value of ([92*] excluding [9203*]) and a zero row (because of the Return 0 in the [92*] formula), but that should not be an issue since the amount of zero rows is limited.

[JackLacava]

I think there might be ways to avoid that too - try returning None or False instead of zero, or throwing an exception.