7 Replies Latest reply on Aug 3, 2017 3:17 PM by Jim Dehner

    How do I convert a column of data?

    Danielle Anderson

      I have 2 tables, that have no connection to each other.  The first contains 2 columns - a code, and a description. (for example: 1 and Description1 ; 2 and Description2; 3 and Description 3; etc)

      In the second table, I have a column (that we will call 'Characteristics') that contains 1 to many codes, separated by commas. (for example:  1 ; 1,2,3 ; 3; etc) - the largest list I have in my table, to date, is 17 codes in the Characteristics field on a single record.

      When I display the column 'Characteristics' in the text table, I want the Descriptions to appear (separated by commas), not the codes.

      How do I do that?  Can you point me to the correct training video perhaps, or easily explain (I am new to tableau)?

      Thank you.

        • 1. Re: How do I convert a column of data?
          Jim Dehner

          Hi Danielle

          This is a little confusing -

          If I have this right  you have a table with 2 columns the first are integers and the second is a text description

          the second table is just a single column of integers separated by commas

           

          based on your narrative I'm getting the sense that the integers in the first table do tie back to the integers in the second table - and you want the actual text to replace the integer numbers -

           

          Couple of questions - in the second table what does each record represent - e.g. a customer, a product, a patient - ?

          You say there is no connection from table 1 to table 2 but aren't the integer values related?

           

          Tableau is fundamentally different than excel in that it is data base oriented - you work with dimensions and measures - dimensions classify and group data - measures are the actual values.  I would suggest you look at      Tableau Training and Tutorials

           

          Jim

          • 2. Re: How do I convert a column of data?
            Danielle Anderson

            I am working with an oracle database.

            The 'code' field is not an integer, it's a string.  My example may have eluded to it, but some of the codes are 'ERT' or '13PAR' or '12'.

            The second table is a table of tasks.  The codes are attributes of the task that are not 'important enough' to have a specific column in the database for - meaning stuff that we want to know about, yet don't want to waste time and space in the gui for individual fields.....so in the application, it's a text box full of attributes(that display the description not the code) that the user can double click to move to an empty box on the other side of the screen.  The user is required to select at least one attribute per task.  When the task record is saved, it creates the field of "<code1>, <code2>, etc" in the table2 "characteristics" field.

            They do tie back, but you cannot 'join' them.....or at least, I don't know how....

            • 3. Re: How do I convert a column of data?
              Jim Dehner

              OK I still a bit confused but becoming clear ( I have a background in Oracle so some of this is familiar)

               

              still not certain what kind of record(s)  we are talking about here but I will draw an analogy just to help clarify the issue in my mind

              You have some sort of Master Record and in that Master record there are fields that are important and coded on all  or moat records and then there is something akin to a free form field where the users selects at least one "task" for a large but limited number of codes and those codes are  comma separated.  Am I getting close?

               

              When you use the master record you may need to use the "Task -descriptions"  in a tableau field but you want them to be the actual description and to be separate string fields?

               

              Not certain how to do that but I will give it some thought

               

              Jim

              • 4. Re: How do I convert a column of data?
                Lisa Li

                Hello Danielle,

                 

                I'm not sure if I am completely understanding you. I built out sample tables below, please let me know if it is similar to what you are trying to solve and if not, please provide a sample table so we can better understand your requirements and needs.

                 

                      

                Table 1Table 2Table 3
                CodeDescriptionCodeTaskTaskDescription
                Code 1Description 1Code 1Task 1, Task 2, Task 3Task 1Description 1,Description 2, Description 5
                Code 2Description 2Code 2Task 1, Task 4, Task 5, Task 6Task 2Description 1, Description 5
                Code 3Description 3Code 3Task 7, Task 8Task 3Description 1, Description 5
                Code 4Description 4Code 4Task 9, Task 10Task 4Description 2, Description 5
                Code 5Description 5Code 5Task 1, Task 2, Task 3, Task 4, Task 5, Task 6Task 5Description 2, Description 5
                Task 6Description 2, Description 5
                Task 7Description 3
                Task 8Description 3
                Task 9Description 4
                Task 10Description 4

                 

                If Table 3 detailed above is what you want, you should use a delimiter to split out the tasks so Table 2 looks more like below.

                Table 2
                CodeTask
                Code 1Task 1
                Code 1Task 2
                Code 1Task 3
                Code 2Task 1
                Code 2Task 4
                Code 2Task 5
                Code 2Task 6
                Code 3Task 7
                Code 3Task 8
                ......

                 

                That way, you can join by Code. You'll have multiple rows for each Task but at that point you can aggregate Descriptions so there is only one row per Task.

                 

                Hope this helps!

                Lisa

                • 5. Re: How do I convert a column of data?
                  Danielle Anderson

                  no.  there are not three tables.  Only 2.

                   

                  Table 1:  columns CODE, and DESCRIPTION

                  1 | Description 1

                  ERT | Description 2

                  13PAR | Description 3

                   

                  Table 2:  columns TASK_ID, TASK_CODES

                  12345 | 1

                  45745 |  1, ERT, 13PAR

                  66457 |  ERT

                   

                  What I want displayed:

                  12345 | Description 1

                  45745 | Description 1, Description 2, Description 3

                  66457 | Description 2

                   

                  I too, thought about split, but split is limited to 10 splits....where I do have records with more. If I didn't have any records with more than 10 splits, then I could have created a new calculated field that concatenates the string back together and then displayed that new column instead of the raw one from the database.

                  • 6. Re: How do I convert a column of data?
                    Lisa Li

                    Split in Tableau is limited to 10.

                     

                    Have you tried split in Oracle or even writing a python or R script to parse out the data?

                     

                    There are a lot of resources detailing how to. For example: regex - How can I split this comma-delimited string in Python? - Stack Overflow

                     

                    -Lisa

                    • 7. Re: How do I convert a column of data?
                      Jim Dehner

                      Now that I understand your goal I think Lisa may have your solutions

                       

                      Jim