Forum Discussion

saz's avatar
saz
New Contributor
2 days ago

is there a way to check the total value on each account in the staging?

Hi Community,

We have a requirement to bypass lines from the source data for each account if the total equals to zero, regardless of the UD1 value. For example if these two lines are in the source data:

Account 1, UD1_1, -100

Account 1, UD1_2, 100

we want to bypass account 1.

Is there a way to achieve this result? in our case this is happening because we are adding UD1 in the source system and it's triggering a lot of unused accounts to now appear on our staging table because of the different UD1 members.

Finance

2 Replies

  • MarcusH's avatar
    MarcusH
    Valued Contributor
    2 days ago

    Derivative Rules are the normal way of aggregating source data records. The usual way of using them though is to map the net position ie you aggregate the source into a single record which then gives you a negative or positive amount. You then map that as a credit or debit to the relevant account(s) and set the source records to Bypass. I am assuming that you want to map the source records and not the aggregated derivative record. I would create a derivative record to execute the aggregation. Set the Derivative Type to Interim so that it does not get loaded to the cube. Then on the Account dimension create a map (not One to One) with the Logical Operator set to Business Rule (I don't think a Complex Expression will work on Derivatives but I might be wrong). The Business Rule reads the value of the Derivative record; if it's zero the result is Bypass, if it's not then the result is the Account Cube member.

  • BenEppel's avatar
    BenEppel
    New Contributor III
    2 days ago

    If you are using a Connector to load the data, you could build logic within that rule to not bring in records that fit this case.