Identify Incorrect Amount

I am trying to identify if the amount entered is in correct format and also if it is Blank or not. In order to achieve that I am importing the amount in both text and numbered line item.

- Below are the different results we get when we import the amount in different formats in text and numbered line

 

AMT_TextAMT_Number
12,3/4512345
12,345.4612345.46
64,366436
A0
12A12
0.1A0.1
//1//\1
AS123123
//0.9870.987
Ab//7676
;'/11
we342342
12//321232
1234512345
12////12
12//131213
//12.23412.234
0.980.98
.//980.98
,87,898789
123.234123.234
0.77640.7764
12,23,4.4512234.45
1,34,235.66134235.7
12..130
12>>131213
 0

 

Can someone please share some idea on how to identify the invalid entries from the valid ones?

Answers

  • Hi @NilanjanDutta 

     

    First point here is Amount should always be imported in a number formatted line item . That is why you are seeing some texts and characters in text formatted line item . 

    If u want to make out which are entered correctly , my doubt is what type of number(Amount) you would like to see . 

    only Whole number or decimal is also Ok for you .

    You can get rid of Decimal points in format itself . While selecting number format you can opt out Decimal point to be zero . 

    and if u have not added any amount , the number format line item still would take 0 or blank depending upon the format you were selected (Zero Format) . 

    You can get to know if the numbers are blank or not is by having boolean line item and check for value "0" or Blank . and can be entered correct value from registry(if you have data) for those in a separate line item . If only finding out is OK means Boolean is enough . 

     

    and also while importing , it imports only correct formatted amount only . 

    Let me know any further assistance is needed on this . 

     

    Regards,

    Puneeth HP 

    Success is the Intersection of Dreams and Hardwork!

  • Hi Puneeth,

    Thanks for your input.

    Just wanted to highlight the client requirement - He is currently trying to upload the text formatted values and is expecting the the system should show an error since the format is not right. But once we are uploading the file in Anaplan the system is amending the numbers accordingly instead of throwing 0s which is becomes a problem to identify to right ones from the wrong ones.
    Hope I am able to articulate the problem correctly. Let me know if any further clarity is needed on this .

    Regards,
    Nilanjan
  • Hi @NilanjanDutta 

    Correct.png

     

    In that case you can do like this . 

    Text line item is import as you mentioned. 

    Number line item is a number formatted line item with formula = VALUE(Text) . which gives only valid numbers .

    Correct is boolean . formula = IF Number > 0 then FALSE else TRUE ... If you want zero value to be opt out here then you change the condition >= 0

    Hope this helps .

     

    Regards,

    Puneeth HP

    Success is the Intersection of Dreams and Hardwork!

  • That's a good work around, Thanks
    But what about numbers like 12,345.45 ?
    Value(Text) cannot convert the above number which is a valid format
    Basically where ever there is a comma, Value function doesn't work
  • Yes @NilanjanDutta Thank you .  I am aware of this . 

    Anyway to avoid this, minimal data manipulation should happen is what my concern . 

    We can still achieve this by taking out Comma and it will be adding more complexity to data is what I feel . 

     

    Thanks,

    Puneeth HP 

    Success is the Intersection of Dreams and Hardwork!

  • Thanks Puneeth for your valuable input

    Regards,

    Nilanjan