Wednesday, 8 October 2014

Using Parameters to search a free text field

One of the pet hates of anyone working in data is that dreaded free text field. You know the one, every system has one. That little white box that just invites the user to enter what ever little collection of text they want to add. In an ideal world all data entry would be a drop down box or a selection of phrases so that you could do some data analysis on it. However, thats a dream world, full of fluffy clouds, endless summer days and cool drinks on a warm beach. In reality we have records in databases that are strings of text that contain useful data if only we could group it together.

I was asked at work this week if I could provide some analysis on a free text field. At the Welcome Trust Sanger Institute we have a large DNA Sequencing facility that produces huge amounts of data. This data is produced by running DNA samples on a number of Illumina DNA Sequencers, each run takes about 5 days and produces the equivalent of 100 peoples genomes. Each run gets QC'd by a real life person who will annotate some runs if there are interesting things to note. This annotation is a free text field that we would like to be able to use to monitor for potential problems. So heres how i did it. 

First we need a parameter to act as our search entry. We create a parameter and change the data type to string and set the allowable values to all. Leave the current value blank.

Next we create a calculated field based on the parameter, this will then allow us to use the results of the search to filter our data. 

In my data set the text is in the [lane_annotation] field so we create a calculation using the CONTAINS(string1,string2) function. What this does is search through the first field, string1, looking for the text in string2. Bit like searching for text in a text editor. If it find a match then it will return true or if not false. In my case i am searching the [lane_annotation] field looking for text entered in the [Parameters].[Annotation Search]. If it matches it will return "Annotation" and if not "None"

Next i build my viz.

So we have the number of lanes that have been QC'd each month since 2011. So now we can use the calculated field we created earlier and place it on the colour shelf. 

So now we can see the number of lanes with and without annotations. This is useful as it shows that a spike in the number of annotations might just be due to an overall increase in lane throughput. This is using just the calculated field, we've not used the search parameter yet.

Now add the parameter control to the viz and we can search for something eg "uneven"

Now the search string in the parameter is being passed to the calculated field and gets evaluated. All the 6000 or so records are checked and those that contain the word "uneven" are tagged with the "Annotation" flag. We now we have a line chart that shows the occurance of the word "uneven" in the [lane_annotation] field

I then decided to add a second field and look at the % of total

To finish it off i added it to a dashboard along with a detailed view of each lane and annotation and used a dashboard action to filter the results. So now we have a little search tool that we can use to go back through a couple of years worth of data, looking for text in a free text field and look for potential issues.

I've already used this twice this week already so hopefully you might find this useful for getting information out of your free text fields.


Author & Editor

Tableau Zen Master, Social Ambassador, Wrangler of Data, Vizzer of Data


  1. Extra tip - using the LOWER or UPPER functions on both the parameter and the text field will make sure its not a case sensitive search

    1. Actually I've done that on assumption that its required, but actually i don't think it is :-)

    2. This may not sound fair, because it’s not

      But did you know that you can be a guy’s dream girl...

      I mean, you can literally check off every box on his “perfect woman” list...

      But if you mess up this one thing, he’ll drop you the second another option comes along?

      My friend James Bauer discovered this missing “secret ingredient” all men are constantly searching for in a woman.

      And most women have no clue it exists because guys aren’t even aware of it.

      We just KNOW when it’s missing.

      ===> The “Secret Ingredient” to obsessive love <=====

      The really cool thing is, when you know how to give a man this “secret ingredient”...

      It will send a shockwave of desire for you straight to his brain and he will HAVE to have you.

      In fact, when you do this... watch his face light up, almost as if he’s just been zapped.

      It’s that moment when he says to himself “Where have you BEEN all my life?”

      Every woman should know this. Check it out here: ====> Why men leave “perfect” women... <=====

  2. Nice article, thanks for sharing!

  3. Nice article, Thanks for sharing with us!!!

    Visit -

  4. It was really a nice article and I was really impressed by reading this article. Tableau Training Videos

  5. Your blog has given me that thing which I never expect to get from all over the websites. Nice post guys!

    Melbourne SEO Services

  6. Truly a very good article on how to handle the future technology. After reading your post, thanks for taking the time to discuss this, I feel happy about and I love learning more about this topic
    Tableau Training Videos

  7. Your blog has given me that thing which I never expect to get from all over the websites. Nice post guys!

    App Developer

  8. Come and see how 1,000's of individuals like YOU are making a LIVING by staying home and are fulfilling their dreams TODAY.


  9. I read this article. I think You put a lot of effort to create this article. I appreciate your work.
    thesis Writing Service

  10. I read this article. I think You put a lot of effort to create this article. I appreciate your work.
    thesis Writing Service

  11. This article is worthy of attention. I recommend reading it to the end.


  12. Thanks for sharing this great article..Its really nice and useful

    Machine Learning in Classroom training in Bangalore

  13. Being a Website Design agency in Sydney I wanted to thank you for this great article, which is very much useful for Web Designers