Not working - Decimal Sum 10s

[JDong]

Hi Team,

I have the following data sample in my column 

Value

5,25

7,12

1,08

0,42

0,08

3,13

I want to add all these and create a subtotal. I have attached a sample flow in this email where the sample works fine.

I am applying this in my real world scenario and it does not work as expected. 

Instead the summation values are shown rounded off to nearest number or as 0

On trying to debug I am not able to figure out why this is happening since both seems to be using same logic. Any idea to identify the root cause ? End result should be the subtotal as attached.

Thanks

[kelsey_kincaid]

@JDong ,

Does the data look OK before going into the Summarize tool or is it rounded off to zeroes before then? Have you double-checked your Select tool to make sure your 'Value' field is set to a FixedDecimal and not an integer? 

[apathetichell]

Do you have user settings/localization on comma for decimal? If so - you shouldn't need to do a tonumber() conversion.

I also wouldn't use the same column name for my conversion and instead create a new fixed with or double (preferably) column and use that for sums, or I'd use a multi-field formula to change the field-type and tonumber() in the same tool.

Basically you're taking in a comma for decimal number, putting that in a tonumber() formula but then back into a string field and then using select to transfer that to fixed with - if something has too many decimals or other oddities there could be an issue on the forced number to string and then subsequent string to fixed-with conversion

[JDong]

@apathetichell @kelsey_kincaid 

Thanks already for the support

Level	Value
5.100	0.000000
5.110	0.720000
5.120	0.170000
5.130	0.030000
5.140	0.190000
5.150	0.120000
5.160	0.000000
5.170	0.140000
5.180	0.000000

The first line output must be 1.37

Here is what is happening with my dataset. The top level is 5.100 which I where I need the correct value is showing as 0.000000. Seems trivial but not sure why this is happening. Is it also possible that grouping of other fields impacts this. Infact I have another column where the sum values pulls up fine. Have issue only with this particular column.

Thanks 

[JDong]

@kelsey_kincaid @apathetichell 

The data looks ok going into the summerize tool and is of the format Ex: 5,27 (comma is the delimiter) being used.Now the approach that @apathetichell suggested  have used V_Wstring and second step uses the multirow forumla.

The input data is V_WString and hence the multirow works now

[danilang]

Hi @JDong 

Where does the Level column come from?  It's not in your original workflow.  Can you please add in a sample of this new data and explain how the summarization is related to the level?  

Dan

[JDong]

Hi @danilang @apathetichell @kelsey_kincaid 

Please refer to the attached data and thanks for the push so I have spent time on the masking the actual data and the problem statement is more clear now.

I have filtered on one Class 1 and we can see right away the columns marked in red and not summing up as expected, but the other green columns work fine.

This is exactly the issue I am trying to resolve and we can try looking only at Value 8 which will fix all other columns. 😀  The format needed is say Ex :

107.223,85

Thanks !

[danilang]

Hi @JDong 

If the green columns are working correctly and the red ones aren't, there must be a difference in the method that you use to summarize them.  Do you perform the summarization on each column individually?  Please post a sample of your workflow.  Trim it down so it only shows the process for one of the green columns and one of the red columns.  

Dan

[JDong]

Hi @danilang 

The summerization is done for all these in one transformation. I just have one summarize tool at the end and have added all columns to summerize.

I have other summerization in the larger flow and what I tried is to fix this issue where the flow originally attached in this question summerizes just green columns. 

If you notice the Summerize one in the top...that is where all the fields first flow and I am grouping by levels and summerizing all fields. Here itself the values are shown as 0. Before it enters the summerize step in the select module I convert the V_WString fields to double and these are the red and green fields and also since I am calculating the subtotals. (Using float and fixed decimals here in the casting return 0 for all values)

So is there a reason Value 8 column does not summerize and shows 0 ?

Hope this helps !

Thanks

[danilang]

Hi @JDong 

If that one summarize tool handles all the columns and some columns are working and others aren't, look at the data going into the summarize tool.   Examine the data types and values just before the summarize.  If you're using the preceding Select tool to cast from string to double, it's possible that some values have characters that cause the conversion to output 0 instead of the value.

Dan