How to identify zeroes uploaded by users vs never uploaded

lilasququ
lilasququ
in Modeling

I searched for the posts regarding zeroes vs null values but don't think I found an answer to my question.

 

So in module A, when people create projects they input values. These values are brought to module C as reporting values.

 

In module B, people have the ability to upload values from Excel. The formula in module C says , if values uploaded from module B is zero, use the values from module A, otherwise use the uploaded values from module B. So here I don't know how I can create a formula that recognize whether it's a zero from module B (truly uploaded from Excel) or it's null (people never uploaded value from Excel to this project).

 

Any help is appreciated. Thank you.

 

Tagged:

Answers

  • TimWard70
    TimWard70

    I've not found a difference between no input and 0 either I'm afraid.

     

    It's not very elegant, however, you could load the values from Excel into text formatted line items.  Then you can test the contents of the line item with an ISBLANK to see whether it has been populated by the upload.  You would then need to take the VALUE of the line item when you pulled it through into your calculation.

  • MarkWarren
    MarkWarren

    I'd advise against using text for this, will have an impact on performance.
    If you need to do this you could have module B dimensioned by Users, then you can add a boolean line item to module B that will be a column in the upload that defaults to True. The boolean line item will be need to be reset before the upload.
    This way you can then know exactly what that user has uploaded and in module C you can know that the zero value has come from a row that has been uploaded.

    You don't have to use the Users list, but you could have a situation where 2 user upload at the same time and potentially change data if their imports interleave during a running process.

  • lilasququ
    lilasququ

    You mean add it to the upload action to turn the boolean line item to true ? 

  • E2EJonathan
    E2EJonathan

    If you create an action/import that replaces all 0's with 0.00001 in module B before export to Excel. You can then look for true 0's in the import in model C.  Then you also need to replace all the .00001's with a 0 later in your calculations. It is an **** solution that may confuse users, but Excel can hide the .0001's pretty well with some formatting.  

  • rob_marshall
    rob_marshall

    @lilasququ 

     

    Take a look at this, it might help you...

     

    https://community.anaplan.com/t5/On-Demand-Courses/Delta-Data-Loads/ta-p/81536

     

    Rob

Categories