Identify Incorrect Amount

NilanjanDutta_*20530

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_Text	AMT_Number
12,3/45	12345
12,345.46	12345.46
64,36	6436
A	0
12A	12
0.1A	0.1
//1//\	1
AS123	123
//0.987	0.987
Ab//76	76
;'/1	1
we342	342
12//32	1232
12345	12345
12////	12
12//13	1213
//12.234	12.234
0.98	0.98
.//98	0.98
,87,89	8789
123.234	123.234
0.7764	0.7764
12,23,4.45	12234.45
1,34,235.66	134235.7
12..13	0
12>>13	1213
	0

 

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

Puneeth H P

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 

NilanjanDutta_*20530

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

Puneeth H P

Hi @NilanjanDutta 

 

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

NilanjanDutta_*20530

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

Puneeth H P

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 

NilanjanDutta_*20530

Thanks Puneeth for your valuable input

Regards,

Nilanjan