sign flip in complex expression

photon
New Contributor III

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.

11 REPLIES 11

OS_Pizza
Contributor III

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.

OS_Pizza_0-1677748792673.png

Or else if you want to flip sign inside the complex expression. Just multiply the result with -1 and return it.

 

ChristianW
Valued Contributor

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.

JackLacava
Community Manager
Community Manager

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
New Contributor III

So, I guess this is the real answer even if it's not a solution. What I need is impossible. How encouraging.

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.

photon
New Contributor III

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.

No worries. Another thing you can consider is dynamically building a TR rule. You said you didn't want to manually maintain a rule with 1500 1:1 rules but you don't have to necessarily manually maintain it if you go down this path.

I just recently built something in a project that sucked in some meta-data from SAP4Hana via REST and dynamically built some TR rules for example.

You can also consider lists rather than many individual 1:1 rules when doing this dynamically. In my scenario this was faster.

photon
New Contributor III

You know, I so rarely find a use for Lists that I forget about them even when they would be helpful, like this. That's way better than a bunch of 1:1 and I had never considered the performance impact.
Building a dynamic rule seems like a reasonable compromise but I'm punting it back to the source admins before I dump any more time into this.

Thanks for the ideas.

JackLacava
Community Manager
Community Manager

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):

  1. 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)
  2. mask in Rule B with order 2 has Source __RV__* and Target *, with Flip Sign on

aricgresko
Contributor III

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:

aricgresko_0-1677776488492.png

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
New Contributor III

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.