Skip to main content
A
AndreaF
Expert
July 3, 2023
Solved

Exclude members from rule expression

  • July 3, 2023
  • 2 replies
  • 0 views

Hi,

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

image.png

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

Best answer by JackLacava

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

 

 

 

2 replies

RobbSalzmann
RobbSalzmann
Legend
July 3, 2023

Try using a couple interim totals:RobbSalzmann_3-1688419260494.png

 

 

 

 

A
AndreaFAuthor
Expert
July 4, 2023

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?

J
OneStream Employee
JackLacavaOneStream EmployeeAnswer
OneStream Employee
July 5, 2023

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

 

 

 

M
MarcusH
Expert
July 4, 2023

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

A
AndreaFAuthor
Expert
July 5, 2023

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.

J
OneStream Employee
JackLacavaOneStream Employee
OneStream Employee
July 6, 2023

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

Terms & ConditionsAccessibility statement