How to encode 10,000 colours to in one hit within Tableau

In this blog I am going to introduce you to how I quickly assigned 1156 colours to the colour specified by each cell using a neat XML hacking trick for the below visualisation.

colourofmusic.png

I will show you how to do this using Alteryx and notepad, and just alteryx, though the process would be repeatable in other platforms such as Excel.


First things first, we need to open our data source in Tableau and bring our ‘colour’ dimension into the view. Here you will see that my dimension members represent hex codes.

Assign Colours.png

We then need to bring our Colour pill onto the colour shelf also. This process will then automatically assign Tableau’s default colour palette to your data set.

Now a quick note at this point is then when you use the ‘Automatic’ colour palette, the assignments of the colours to your dimension members is not hard coded into the XML. It essentially reassigns them automatically every time it opens.

However, if we were to change the colour palette, and instead assign the pallet ‘Tableau 20’ instead and then open the XML you will see that these colours are now assigned ‘hard’ to the dimension members.

colour.png

From this you can probably recognise how we can ‘attack’ this problem of quickly assigning colours.

All we need to do is generate 3 rows of XML for each dimension member.

<map to=’COLOUR’>

<bucket>&quot;DIMENSIONMEMBER&quot;</bucket>

</map>

So for our first colour in the list we would amend the XML to instead of reading…

<map to=’#4e79a7′> (also known as Tableau 20 dark blue)

<bucket>&quot;0a0a0a&quot;</bucket>

</map>

To read…

<map to=’#0a0a0a ‘> (also known as the actual colour of the dimension member)

<bucket>&quot;0a0a0a&quot;</bucket>

</map>

And as a result when we open the tableau workbook we get this…

kmortg.png

So now we just need to repeat this process for each of our individual colours.

Thanks to Alteryx this is easy.

Take a copy of all the values that exist within this dimension. If you have Alteryx at your disposal you will likely input your data and simply group by the dimension within your Tableau view.

sdf.png

As noted further up in this post, we need 3 lines for each Colour. In order to do this in Alteryx we can simply use the ‘Generate rows’ tool to do this, configuring it as below.

1.png

2

Now we can build out the XML. We will use our new RowCount field to acknowledge how to manipulate each row and a formula tool to do this manipulation.

The expression is relatively easy to understand.

3.png

We can now simply take our output from this formula tool and replace the encoding XML that exists within our Tableau Workbook using a text editing tool. Once you save it, and open your new workbook you will see that your ‘colour’ dimension field now correlates correctly with the colours assigned to them.


Is it possible to automate the replacement of the old and new colours with Alteryx? Well yes it is, and again it is relatively straight forward.

First things first we need to input our tableau workbook into Alteryx. You can follow my blog post on how to do this here.

We then need to RecordID our XML, the reason for this will become clear later.

Secondly we need to acknowledge the lines of the current mapping of the colours to your dimension.

In order to do this we need to identify the ‘encoding attributes’ tag appropriate for this dimension.

Within my workbook this line looks something like…

<encoding attr=’color’ field='[none:Colour:nk]’ palette=’tableau20_10_0′ type=’palette’>

Our colours are then assigned using the three line ‘map to’ technique between the above opening tag, and the below closing tag…

</encoding>

So we need to acknowledge these lines and anything inbetween. To do this with a combination of a formula tool and a multi-row formula tool.

2017-01-04_17-34-27.png

Initial expression to identify the encoding tag for this field (this will be different for each dimension that you may have placed on the colour shelf so you will need to dive into the XML to identify what this tag looks like).

5

Secondary expression used within the multi-row formula tool in order to acknowledge the mapping lines that exist between the opening and closing XML tag.

6.png

Now our secondary field ‘TagMiddle’ which is a result of the multi-row formula tool contains only the ‘mapping lines’ from our XML code so we can simply filter our data stream to remove any lines where ‘TagMiddle’ is equal to 1.

HOWEVER, in order to dynamically acknowledge where our new encoding information needs to be inserted we must acknowledge the position of this old XML. In order to do this we will take a sample of one line from our ‘remove’ stream and append this line, along with it’s RecordID (which in actual fact is essentially a position ID), to our new encoding XML.

We can then take this and union with our untagged XML stream and sort them into the correct order using our RecordID field.

workflow.png

It is then a case of just outputting our data as a .twb file, which can be done using the following configuration.

Output.png

Now you can close Alteryx and open your .twb file and you will see all of your colours have been automatically passed into the raw XML of the tableau workbook.

Magic.

The workflow that I have built for the purpose of writing this blog can be found here.


You may be wondering why I stated a limit of 10,000 colours; well this is because when trying to encode ~11,000 colours using the above technique I identified that not all my information was being passed and maintainied within the XML.

I am currently awaiting for Tableau to reply to my tweet to explain…

2017-01-04_17-37-16.png

But I really doubt it will ever be an issue.

Ben

 

Advertisements

One thought on “How to encode 10,000 colours to in one hit within Tableau

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s