1 2 Previous Next 15 Replies Latest reply on Aug 31, 2018 3:39 AM by Simon Runc

    Visualize Characters in a String with Different Colors

    Fabian Deitelhoff

      I'm using Tableau Desktop. In an F# script I generate strings like the following one:

      ABBDCFFHIJJJJKKJJJJMLKKMLKMLMONONONOOPPOPMPPOONMO

      I then generate HTML snippets to visualize every character with a different color. Is there a way to do this in Tableau? I think it's possible but I haven't manage to came up with a solution yet.

      The current HTML visualization looks like the following one:

      aois-dna-html.png

        • 1. Re: Visualize Characters in a String with Different Colors
          Simon Runc

          hi Fabian,

           

          So to make this easy in Tableau, I'd advise to restructure your data into a list, with each character on a different row, like this

           

           

          Once it's like this you can use turn off the Aggregate measures

           

           

          and then it becomes pretty simple.

           

          Hope that helps

          1 of 1 people found this helpful
          • 2. Re: Visualize Characters in a String with Different Colors
            Fabian Deitelhoff

            Hi Simon,

             

            That looks very nice. Thank you!

             

            One question: I want to visualize many of those strings. How should I arrange the table? Duplicate the rows (with other names)? The dates are nice and I could use them as marker for the data (this line is participant A, the other B and so on). This isn't possible with the current layout or am I missing something?

             

            Best,

            Fabian

            • 3. Re: Visualize Characters in a String with Different Colors
              Fabian Deitelhoff

              Current solution: Use two columns with different data. That's working quite well.

               

              So thank you for your answer!

              • 4. Re: Visualize Characters in a String with Different Colors
                Simon Runc

                Coolio!

                 

                Looks like you've got it already, but yes you just repeat below (Tableau is a SQL database, at heart, so loves normalised data!)

                 

                So something like this

                 

                 

                and the date field just becomes a dimension (I've shown this in the attached)

                • 5. Re: Visualize Characters in a String with Different Colors
                  Fabian Deitelhoff

                  Hi All,

                   

                  I've discovered a serious problem in the Tableau solution:

                   

                  The marks are sorted as you can see in the following image.

                  tableau-marks-sorted.png

                   

                  As you can see in the first image I posted that is highly unlikely! I need the colors schemes as hey appear in the data source. For example, for participant 114 (you can see the line in the image) the data looks like this string:

                  tableau-original-data.png

                   

                  But the image above is sorted like all "A's" first, then "B's" etc.

                   

                  Can I sort the marks (or don't sort them at all), so they appear like the data in the data source? This is very important for my future analysis, or I've to switch to another tool/build it by myself.

                   

                  Best,

                  Fabian

                  • 6. Re: Visualize Characters in a String with Different Colors
                    Simon Runc

                    hi Fabian,

                     

                    So I think you'll need to add some sort of ordering field to the data....in the attached I've used this set of data

                     

                     

                    Once we've done this, we need to create a merged field of the Letter and the RunCode...this is so Tableau can sort each RunCode separately (rather than just each letter, over the whole dataset)

                    I created it like this

                    [Character - RunCode]

                    [Character]+str([Run Code])

                     

                    we can then bring this field into the level of detail, and sort it like so

                     

                    And finally as I think you want your data disagregated, we can do that in the analysis option (untick Aggregate Measures)

                     

                     

                    Hope that makes sense, and help.

                    1 of 1 people found this helpful
                    • 7. Re: Visualize Characters in a String with Different Colors
                      Fabian Deitelhoff

                      Hi Simon,

                       

                      Thanks for your help!

                       

                      I tried to replicate it but it's not working with my data. At least not completely, because there are characters that are in the data source order and others that are not. I've no clue what's going wrong.

                       

                      I'm using this test data source:

                       

                      test-data-source.png

                       

                      The first line is "Participant" (your "Run Code" field), the next four are irrelevant for this error. Then the "AOI_HIT" field (your "Character" field) and last but not least the "Order" field. This is the result:

                      test-data-source-viz.png

                      The first characters are in order but it starts with the "J". According to the rows 7-9 there should be 3 and not five (the "Js" and "Ks" are already in wrong order).

                       

                      I've double checked the settings but I cannot see any errors/differences to your example.

                       

                      test-data-source-settings.png

                       

                      Calculated field:

                       

                      "AOI_HIT - Participant"

                      [AOI_HIT]+str([Participant])

                       

                      test-data-source-sorting.png

                       

                      Maybe you have an idea or you can see my mistake(s).

                       

                      Best,

                      Fabian

                      • 8. Re: Visualize Characters in a String with Different Colors
                        Simon Runc

                        hi Fabian,

                         

                        Looks like we're getting closer. So the key here is to create unique "codes" (identifiers) for each row of your data. So in the "J" example as we have only created the field unique identifier as

                         

                        [AOI_HIT]+str([Participant])

                         

                        the "J"s that come later just get grouped, next to the other ones...my bad on this!

                         

                        If you amend this field to be

                         

                        [AOI_HIT]+str([Participant]) + STR([Order])

                         

                        Bring this field into the level of detail (replacing the [AOI_HIT]+str([Participant]) we had before), and setting up the sort (on Min order) as before)

                        that should ensure that you get 3 Js, followed by 2, later on.

                         

                        Let me know if that doesn't do the trick

                        1 of 1 people found this helpful
                        • 9. Re: Visualize Characters in a String with Different Colors
                          Fabian Deitelhoff

                          Hi Simon,

                           

                          Many thanks for your endless effort!

                           

                          Adding the Order field does the trick. The visualizations are looking good right now. Will check them, but at first glance everything seems fine.

                           

                          Best,

                          Fabian

                          • 10. Re: Visualize Characters in a String with Different Colors
                            Fabian Deitelhoff

                            Hi Simon,

                             

                            One follow up question:

                             

                            To get the correct order, the calculated field has to be the first in the mark section, right? A few minutes ago I changed the visualization to use the colored squares but the ordering was gone. Switching the mark sequence to be the order calculated field first, everything is fine.

                             

                            Can you explain this or point me to a useful link? This was new to me and I think one has to know this.

                             

                            And how did you get the colored squares and text combined? I only have them vertical (first line squares, second line text). But the text within the squares is a nice visualization.

                             

                            Best,

                            Fabian

                            • 11. Re: Visualize Characters in a String with Different Colors
                              Simon Runc

                              hi Fabian,

                               

                              So yes the order of the pills is important, and you want our concatenated field ([AOI_HIT]+str([Participant]) + STR([Order])) first...else it will group by the first field and then do the sort. The reason is really to do with how blue pills work (I'll refer you to this article, which was the ah-ha moment for me with Tableau https://www.theinformationlab.co.uk/2011/09/23/blue-things-and-green-things/ ). This doesn't full explain why we get this sorting...if you think about arranging the blue pills on the rows shelf, it would group by the first pill, then the second pill....and this is carried forward to the detail and colour tiles too.

                               

                              So to get the text and colour...I added the character to the Label tile too

                               

                               

                              Then it's just a case of centering the text (you can do this by clicking on the Label tile)

                               

                              and then using the Size Tile to ensure the squares are big enough to fit the text (or change the font size down)

                              • 12. Re: Visualize Characters in a String with Different Colors
                                Fabian Deitelhoff

                                Hi Simon,

                                 

                                Sorry for bothering you again.

                                 

                                I, again, found a problem in your suggested solution. It is working for some data sets. For example, the one I presented in my post above. There it is working perfectly:

                                This is awesome. But here's another example:

                                Here's a short overview of the underlying data set:

                                I've no clue, why the order is wrong after the "B" with the order "8". The self-created field produces the following data:

                                Obviously, the order get messed up after the "B28", when the "I218" entry is sorted in, instead of the following "B29".

                                 

                                Can this be a problem of the sorting algorithm? For me it looks like this sort of problem, but I don't know what Tableau is using internally or how I can fix this.

                                 

                                Best,

                                Fabian

                                • 13. Re: Visualize Characters in a String with Different Colors
                                  Simon Runc

                                  Not entirely sure. Can you send me a screen shot of your set up (pill arrangement) and sort.

                                   

                                  Like this

                                   

                                   

                                  I can then try and recreate the issue. Could you also post the data you've posted here in text form (I can then use exactly the same data, without typing it up!)

                                  • 14. Re: Visualize Characters in a String with Different Colors
                                    Fabian Deitelhoff

                                    Hi Simon,

                                     

                                    Thank you for your effort and sorry for my late reply!

                                     

                                    This is my sorting:

                                     

                                     

                                     

                                    And please see attached the twbx file and two data sets (first one seems okay, second one produces the error).

                                     

                                    Best,

                                    Fabian

                                    1 2 Previous Next