Skip to main content
P
photon
Contributor
March 2, 2023

sign flip in complex expression

  • March 2, 2023
  • 4 replies
  • 0 views

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.

4 replies

O
OS_Pizza
Veteran
March 2, 2023

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.

 

C
OneStream Employee
ChristianWOneStream Employee
OneStream Employee
March 2, 2023

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.

J
OneStream Employee
JackLacavaOneStream Employee
OneStream Employee
March 2, 2023

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.

P
photonAuthor
Contributor
March 2, 2023

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

D
DanielWillis
Contributor
March 2, 2023

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.

    aricgresko
    aricgresko
    Veteran
    March 2, 2023

    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.

    P
    photonAuthor
    Contributor
    March 2, 2023

    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.

    Terms & ConditionsAccessibility statement