4 Replies Latest reply on Jun 24, 2013 9:23 AM by Siddharth Surana

# Split one column into two, based on dimensions from another column

Hey Guys

I have a column which has a bunch of numbers and I would want to split that column into 2 based on a dimension field.

For eg. Amount - Dimension

1000 - SFDC

2000 - DD

3000 - SS

4000 - SFDC

Dimension Field - SFDC, DD & SS

Now I would want to split the amounts based on the dimension and create new measure fields

Does anyone have any ideas to do this

Thanks

Regards

Sid

• ###### 1. Re: Split one column into two, based on dimensions from another column

Siddharth,

I did it using the following calculated fields

INT( LEFT( [Amount - Dimension], FIND( [Amount - Dimension], " - " ) - 1 ) )

MID( [Amount - Dimension], FIND( [Amount - Dimension], " - " ) + 3 )

Example is attached.

Thanks,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

• ###### 2. Re: Split one column into two, based on dimensions from another column

This is great. Now I have a very basic functionality question?

Why do we include the -1  (INT( LEFT( [Amount - Dimension], FIND( [Amount - Dimension], " - " ) - 1 ) )) in the calculations that you mentioned?

And the Mid function you mentioned displays characters from the start position mentioned to the desired length. So in my example, if SFDC has 4 characters, how does the +3 work?

I know these are basic questions for you, but it will really help me understand the way these work in Tableau

Thanks

-Sid

• ###### 3. Re: Split one column into two, based on dimensions from another column

Siddharth,

Both the -1 and +3 are arithmetic additions to the value of FIND().  FIND() returns the location of the first occurence of " - ".  Let me give you an example

String:                                         1000 - SFDC

FIND( [Amount - Dimension], " - " ):     5

Last value of Amount:                             4

Therefore, we need to use -1 to get rid of the first character of " - ".

The reverse logic applies for the dimension.  Instead of subtracting 1 to remove the first character, we add 3 to skip the entire " - " substring, then we start from FIND() + 3, which is the first character of SFDC.

Hope this helps,

Associate Consultant

Mariner, LLC

http://breaking-bi.blogspot.com

1 of 1 people found this helpful
• ###### 4. Re: Split one column into two, based on dimensions from another column

You have a really clean way of understanding logic.

-Sid