import a column with alphanumeric into number formatted property

Kanishq17
Kanishq17
in Integrations and extensibility

Hi , 

can you assist me in following , 

so i am loading a file with a column into a number formatted list property , in the csv file along with numbers some entries have text only while some entries have text along with number(as shown) , 

Kanishq17_0-1649093667376.png

 

when i load this file in model , so all text lines are ignored , but somehow anaplan is taking only numbers from the lines where alphanumeric property is there 

Kanishq17_1-1649093859141.png

after running the action for this list item , the property is 2411.2 , 

please guide as to how is this happening and what is workaround for this case

 

Tagged:

Best Answer

  • ryan_kohn
    ryan_kohn
    Answer ✓

    Ideally, you would resolve this in an upstream system -- either in the data source or in your ETL layer.

     

    You can also build in some light transformation in Anaplan to account for a data set like this. In this case, instead of importing the value as a Number, you would import the value as Text, and convert the value into number using Anaplan formulas.

     

    ryan_kohn_0-1649288299956.png

     

    Above is an example of how you could accomplish this. Note that the VALUE() function in Anaplan will return NaN for a non-numeric value, so you'll need to have additional logic to convert those NaN values to zeros using one of the techniques in this article.

Answers

  • ryan_kohn
    ryan_kohn

    This is an interesting one! I tried with a few other values below (importing the text value on the left into the number-formatted field on the right), with very intriguing results. The only one that failed was the one that didn't have spaces between the letters and the numbers. Otherwise, it appears that Anaplan will strip out any "whole" words (e.g. text completely surrounded by spaces), and then concatenate any remaining numbers. I am not as of yet able to find any documentation around this functionality, but I would also recommend submitting a case to support@anaplan.com.

     

    ryan_kohn_0-1649108204944.png

     

     

    As to workarounds for your specific use case, what would be the desired functionality? For example, would you want the data in that row to import in some specific format (other than 2411.2), or would you want an error to be reported and not have the value imported at all?

     

  • Kanishq17
    Kanishq17

    Hi @ryan_kohn 

     

    Oh , thanks for the update , for the workaround yes it should ignore these values and should show 0 . Can you guide on this

     

    I would ask support on this

     

Categories