Collection Ops on columns currency / number formatted

Hello,

I’m reading report data from text file and I create a collection afterwards. All fields are Text type. In this way it recreate the report in collection as it is. The issue comes when I need to iterate into Collection for calculation of those fields are e. g. Currency format. I think I need to convert them in Number and I’m using ToNumber() in front of any math operation. This cause the values are wrong converted and subsequentially the results. Here a sample attached.

PS: Even if I setup Balance Amount and Amount as Number in source collection avoiding any conversion it’s not able to format the value e.g. "0.02-" became "-2".

How can I solve this ?
Thanks.
Br.

Marco.
 

Attachments

  • Coll_properties.png
    23.5 KB · Views: 7
  • Decision_properties.png
    5.5 KB · Views: 6

Pete_L

Active Member
Leave the incoming values as text. In your decision expression, you are correctly converting the text values to number so that you can do math on them. If you need to then store the result back into the collection, first convert the result back to text. There is no built-in formula for doing that, but I just perform a text operation on the number and that converts it to text. For example, you could do Replace([InputColl.Balance Amount], "YYYY", "ZZZZ") and store that result back into the collection (It is now text). Change the string values YYYY and ZZZZ if for some reason there is YYYY in your Balance Amount field (not likely).
 
Hi Pete,
don't know if we are on the same page ... look at Coll_properties.png, converting Text ToNumber(57.60-) would be (-5760) which is wrong ... it should convert to number but keep format ... in case of US = 57.60- or -57.60 (as Text) in case of EU = 57,60- or -57,60. I start thinking it's not number I have to treat but somehitng else ... currency ?
Thanks.
Br.
Marco.
 

Pete_L

Active Member
What is the value of Threshold? I suspect that it is 10000. If so, the calculation is working as coded, and you will appropriately get the results you have seen. -57.60/100 = -0.576 * 10000 = -5760.

I think perhaps you need to revisit your calculation itself. It is not the ToNumber that is the issue. ToNumber("-57.60") = -57.60.

If you want to calculate 10% of Amount, multiply it by 0.1. -57.60 * 0.10 = -5.76.
 

sahil_raina_91

Active Member
The issue is definitely with how US and EU represent their currency in numbers.
US uses .(dot), EU uses ,(comma)

In order to get appropriate results with conversion from Text to Number (57.60- converting to -57.60 OR 57,60- converting to -57,60 ), you need to set your local settings for that specific region.
You will have to force the localization via a code.

One way to do it is to have a code stage with a single line:
Threading.Thread.CurrentThread.CurrentCulture = New Globalization.CultureInfo(Culture_Name, True)

Keep Culture_Name as Input for the code.

"en-US" will set culture to English (United States) which uses .(dot)
"de" will set culture to Germany which uses ,(comma)

Refer : https://docs.microsoft.com/en-us/bi...-parameters-and-types/supported-culture-codes

Finally, you will have to know what conversion you are applying.
If using .(dot) > run the code before calculations with "en-US"
If using ,(comma) > run the code before calculations with "de"
 
What is the value of Threshold? I suspect that it is 10000. If so, the calculation is working as coded, and you will appropriately get the results you have seen. -57.60/100 = -0.576 * 10000 = -5760.

I think perhaps you need to revisit your calculation itself. It is not the ToNumber that is the issue. ToNumber("-57.60") = -57.60.

If you want to calculate 10% of Amount, multiply it by 0.1. -57.60 * 0.10 = -5.76.

Hi Pete,
please file attached maybe my previous pictures put you on an odd track.
Thanks. Br. Marco.
 

Attachments

  • ToNumber.PNG
    23.2 KB · Views: 4
The issue is definitely with how US and EU represent their currency in numbers.
US uses .(dot), EU uses ,(comma)

In order to get appropriate results with conversion from Text to Number (57.60- converting to -57.60 OR 57,60- converting to -57,60 ), you need to set your local settings for that specific region.
You will have to force the localization via a code.

One way to do it is to have a code stage with a single line:
Threading.Thread.CurrentThread.CurrentCulture = New Globalization.CultureInfo(Culture_Name, True)

Keep Culture_Name as Input for the code.

"en-US" will set culture to English (United States) which uses .(dot)
"de" will set culture to Germany which uses ,(comma)

Refer : https://docs.microsoft.com/en-us/bi...-parameters-and-types/supported-culture-codes

Finally, you will have to know what conversion you are applying.
If using .(dot) > run the code before calculations with "en-US"
If using ,(comma) > run the code before calculations with "de"

Hi, yes that make sense ... I'll try and keep you posted. Thanks.
 

Pete_L

Active Member
I can't duplicate your results. When I do it, it works fine. Please see the attached.
 

Attachments

  • ToNumber.png
    25.3 KB · Views: 6
Top