How to identify zeroes uploaded by users vs never uploaded

lilasququ
Contributor

How to identify zeroes uploaded by users vs never uploaded

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.

 

5 REPLIES 5
TimWard70
Contributor

Re: How to identify zeroes uploaded by users vs never uploaded

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
Expert

Re: How to identify zeroes uploaded by users vs never uploaded

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
Contributor

Re: How to identify zeroes uploaded by users vs never uploaded

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

E2EJonathan
Contributor

Re: How to identify zeroes uploaded by users vs never uploaded

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 ugly solution that may confuse users, but Excel can hide the .0001's pretty well with some formatting.  

rob_marshall
Moderator

Re: How to identify zeroes uploaded by users vs never uploaded

@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