When loading data, all of our accounts from source match the names of the account in OneStream so we've always used a *:* rule for the account maps. However, we have a new dataset coming in and we need to flip the sign for those accounts that are of account type Revenue in OneStream: not assets or anything else, just revenue. I have working code that will conditionally do something if it's a revenue account else pass through... but I don't know what that something should be to flip the sign. Other people have posted questions about how to do a sign flip in a complex expression but there are no answers. I definitely do not want to maintain a manual list of 1,500 1:1 rules that do nothing but flip the sign. The complex expression feels like the only scalable solution but this detail is missing.

photon nobody said its impossible to handle what you're asking and people have hinted as much already above, for example Jack mentioned derivative rules. There are ways to solve this. If you can't tag the data prior to it hitting OneStream you still have the information that the accounts are revenue accounts in OneStream which you can look up. I think its generally preferred to do this in the transformation stage which you can do here but you also have opportunities to tag or modify data prior to that as well.

I'm not saying it's impossible for OS to eventually arrive at the correct end state, just that it's impossible to do what I asked.While I appreciate the alternatives proposed by others, they are all bad fits for various reasons.I'd rather deal with it in source or via some kind of metadata integration than turn a clean *:* rule profile into something that no one in the company but me will ever be able to decipher. The complex expression was a minimal dozen lines of code with built in comments/documentation but it turns out that won't work.So, I'll figure out another way.

I am not sure if i understood your query correctly but there is Flipsign option during mapping. You can use that to flip the sign.Or else if you want to flip sign inside the complex expression. Just multiply the result with -1 and return it.

You only need to maintain the exceptions as 1:1 (or if they have a pattern, you can use it as well) with the flip sign option checked (as OS_Pizza explained) and then you do the *:* for the rest.You can control the order of the mapping using the order column.

With transformation rules, you can't manipulate the value in a complex expression - unless you're in a Derivative Rule. You'll have to somehow restrict your match and then use the Flip Sign option others mentioned. Chances are that you won't need 1:1 rules, just be a bit creative: for example, your revenue Accounts might be targeting specific UDs, in which case you can use Composite rules to match a bunch of them for the flip.

sign flip in complex expression | OneStream Community

12 Replies

aricgresko
Contributor III
3 years ago
Do all your revenue accounts start with the same number? Without knowing all your existing transformation rules, you could consider do a Mask type Transformation Rule like this example below:
For reference, our source system's accounts align exactly with OneStream. We have to do flip signs on various account types. Revenue comes out negative in the source system, so we flip sign in OneStream as seen above.
- photon
  Contributor
  3 years ago
  My existing rule is *:* but, alas, my account numbers are not that consistent. I did a quick comparison and I have revenue accounts for almost every number.
JackLacava
Community Manager
3 years ago
With transformation rules, you can't manipulate the value in a complex expression - unless you're in a Derivative Rule. You'll have to somehow restrict your match and then use the Flip Sign option others mentioned. Chances are that you won't need 1:1 rules, just be a bit creative: for example, your revenue Accounts might be targeting specific UDs, in which case you can use Composite rules to match a bunch of them for the flip.
- photon
  Contributor
  3 years ago
  So, I guess this is the real answer even if it's not a solution. What I need is impossible. How encouraging.
  - JackLacava
    Community Manager
    3 years ago
    Don't be disheartened! As I said, it's just a matter of creativity. Yesterday I was a bit busy...
    
    What about this (untested, but to show an approach that should work):
    
    complex expression in Rule A with order 1 takes the account name, looks up whether it's a Revenue; if yes, returns the name with a custom prefix like "__RV__". (You might want to do some caching through globals or similar, to save on database queries)
    
    mask in Rule B with order 2 has Source __RV__* and Target *, with Flip Sign on
ChristianW
Valued Contributor
3 years ago
You only need to maintain the exceptions as 1:1 (or if they have a pattern, you can use it as well) with the flip sign option checked (as OS_Pizza explained) and then you do the *:* for the rest.
You can control the order of the mapping using the order column.
OS_Pizza
Contributor III
3 years ago
I am not sure if i understood your query correctly but there is Flipsign option during mapping. You can use that to flip the sign.
Or else if you want to flip sign inside the complex expression. Just multiply the result with -1 and return it.