14 Replies Latest reply on Jun 10, 2016 6:58 AM by Toby Erkson

# SUM   SPLIT  STRING?

excuse me. in Tableau Desktop + Hadoop, I want to know how to substring and sum the number of text of each rows like row 1

"10.00_PM001,23.50_PM002,17.00_Pr001,24.50_Ps002,11.00_PM001,13.50_PM002"

the result i wanted is the sumation of (10.00 + 23.50 + 17.00 + 24.50 + 11.00 + 13.50)

i've attached an exsample file that contains many rows of text, plese help and sorry for my English.

EX:   (FLOAT(SPLIT([Column5],'_',1)) + FLOAT(SPLIT(SPLIT([Column5],',',2),'_',1)))

ข้อความถูกแก้ไขโดย: wasan chaichana

• ###### 1. Re: SUM   SPLIT  STRING?

is it possible for you to write it in your comfortable language which can be translated using google translate to get the better idea of the problem?

• ###### 2. Re: SUM   SPLIT  STRING?

Hey Ashish

I think what Wasan is trying to say is:

He has a string field as displayed with values he needs to sum

He can write that formula for 1 value in a string 2 values in a string 3 values in a string

but how would he right a formula that would calculate the sum for N number of values in a string where N could possibly be any number.

Say he had 50 values he would not want to change the formula to accommodate 50 values

I would suggest data manipulation before pulling this into Tableau as this is an issue with how data is being stored.

But I think what was an wants to know is there any equivalent of a for loop while loop or some other way where he can wright a simple formula to calculate this.

I think this one is beyond me but will be looking out for possible answers because it would be interesting to see how someone would solve this within Tableau.

• ###### 3. Re: SUM   SPLIT  STRING?

If what we have understood by looking at the picture, your suggestions are perfect.

Data modification/manipulation are highly recommended since it doesn't make any sense storing data this way. This should be managed at ETL end or the way of storing data needs to be changed. Instead of adding all values in the one particular cell one can insert new row for each separate values.

-Thanks

Ashish

• ###### 4. Re: SUM   SPLIT  STRING?

excuse me. in Tableau Desktop + Hadoop, I want to know how to substring and sum the number of text of each rows like row 1

"10.00_PM001,23.50_PM002,17.00_Pr001,24.50_Ps002,11.00_PM001,13.50_PM002"

the result i wanted is the sumation of (10.00 + 23.50 + 17.00 + 24.50 + 11.00 + 13.50)

i've attached an exsample file that contains many rows of text, plese help and sorry for my English.

Thank You.Toby Erkson

• ###### 5. Re: SUM   SPLIT  STRING?

Just whizzing by here, but this looks to me to be an issue for the REGEX Wizard Pooja Gandhi. (Hopefully she'll have time to pull out all those numbers and add them up for you.)

Cheers,

--Shawn

• ###### 6. Re: SUM   SPLIT  STRING?

Thanks for the call-out Shawn! A regex question after a long time

Wasan - I am almost positive there are better ways to do what you are asking for. Either at the data source level or in Tableau. Here is how I would do it. Create a regular expression to replace alphanumeric characters between the underscore(_) and comma(,) like so:

Calculation 2: REGEXP_REPLACE([Column5], '_(\w+),?', " ")

With this you are left with numbers with spaces between them on which you can create a custom split by right clicking on this new calc > transform > custom split. In the popup, hit a space bar in the separator section and select 'all' in the split off section. This creates splits in the calculated field above that you can then add.

Then create a calc to add all the splits. You will want to use IFNULL() because if one of the splits do not have a value, you are going to get null results.

Calculation 3: ifnull(float([Calculation2 - Split 1]),0) + ifnull(float([Calculation2 - Split 2]),0) + ifnull(float([Calculation2 - Split 3]),0) + ifnull(float([Calculation2 - Split 4]),0)

+ ifnull(float([Calculation2 - Split 5]),0) + ifnull(float([Calculation2 - Split 6]),0) + ifnull(float([Calculation2 - Split 7]),0)

+ ifnull(float([Calculation2 - Split 8]),0) + ifnull(float([Calculation2 - Split 9]),0) + ifnull(float([Calculation2 - Split 10]),0)

Final view:

Like I said, there may be a better way but this is what I thought of when I came across this question! Hope this helps.

Pooja!

5 of 5 people found this helpful
• ###### 7. Re: SUM   SPLIT  STRING?

in the Tableau program can use loops?

• ###### 8. Re: SUM   SPLIT  STRING?

click   Extract          Is correct.

Enough change.           Live

Did not pass?

Want to know what caused this?

• ###### 9. Re: SUM   SPLIT  STRING?

Some data sources don't support the SPLIT() function. What is your live data source?

--Shawn

• ###### 11. Re: SUM   SPLIT  STRING?

From the online Desktop manual:

It looks like you exceeded the number.

http://onlinehelp.tableau.com/current/pro/online/en-us/help.htm#functions_functions_string.html

Cheers,

--Shawn

1 of 1 people found this helpful
• ###### 12. Re: SUM   SPLIT  STRING?

do not exceed my cloudera hadoop Impala.

Then there is a new calculation model it.

• ###### 13. Re: SUM   SPLIT  STRING?

It looks like Shawn is correct, you have exceeded your maximum number of Splits because your live connection is to Cloudera Hadoop.  That is what has caused the error(s).  Also, it looks like Bangkok has 12 split values in it so that right there would affect the SPLIT() function in Cloudera.

Please attach your updated workbook so others can see what you've done.  Not sure if this will function as expected in .twbx form.  ???

• ###### 14. Re: SUM   SPLIT  STRING?

wasan chaichana wrote: