3 Replies Latest reply on Mar 9, 2017 7:21 AM by Zach Leber

    Search a database with Synonyms from another list

    lars.kannenberg

      Hi there,

       

      i'd like to create a search function within tableau that uses a list of synonyms and I want to ask if this will be possible (and how).

       

      So far I created such a search with PHP+mySQL and another with Excel VBA, both work well. Let me describe how it works.

       

      There is an input field for a search term (e.g. search termin is hamburger)

       

      The search now goes through a column in a database table called "synonyms" that contains severals rows like

       

      1 mayonaise,ketchup,sauce,dip,mustard

      2 fries,onionrings,salad,chips

      3 cheeseburger,hamburger,doubleburger,chickentower

      4 coke,fanta,sprite,drink,water,beer

       

      The search finds "hamurger" in row 3. Now it loads the complete string from row 3 "cheeseburger,hamburger,doubleburger,chickentower" and puts this into an array with a split function (split by ",")

       

      A loop with Count of Array (4 in this case) now is performed with search for the content of the array in a list of data.

      So that the final search result will display all entries of data containing cheeseburger or hamburger or doubleburger or chickentower

       

      I assume this can work in tableau, but I need some help to get started.

       

      kind regards

        • 1. Re: Search a database with Synonyms from another list
          Zach Leber

          I started by trying to blend the synonym data with the order data and using the split function but you can't use calculated fields from the secondary data source (the synonyms) to filter the primary data source (the orders). For Tableau, you will do better with a real join and an LOD expression.

           

          1. Pivot your synonym data into a relational table, it will scale better anyway.

          categories.png

          2. Join this to your order data so for every order you know it's category (users do not have to know the category names).

          orders joined to categories.png

          3. Create a calculated field using an LOD expression to figure out if an order's category matches the search item's category

          matching category LOD expression.png

          4. Put it all together on a dashboard for testing

          synonym search.png

          I've attached an Excel sheet with the test data and a Tableau 9.3 TWBX.

          1 of 1 people found this helpful
          • 2. Re: Search a database with Synonyms from another list
            lars.kannenberg

            Dear Zach,

             

            thanks for your help and this promising approach. I've tested it and played around a bit with adding orders and synonyms. The way it works looks pretty good, but unfortunately the search does not find the synonyms when the search term is not part of the orders.

             

            Example: I added vine as drink to synonyms. Now i searched for vine and it gave no results. The way it should work is to give the results of all drinks (although vine is not in the order list). And it should also work if someone enters drink as search term, it should show all orders containing a drink of course. I tested it and found out that this behaves irregular. I added the sentence "I like to drink". Search for fanta brings the result, search for coke or does not. search for drink only should the sentence and not the other orders containing a drink.

             

            Another thing that would be essential is that the search term is also found within a row with several columns containing strings such as "I drink a fanta and ate a hamburger, and it was great". If I add this to orders, the search finds this order when searched for fanta, but not for coke or sprite, but this should also come up.

             

            But thanks so far!!

            regards

            • 3. Re: Search a database with Synonyms from another list
              Zach Leber

              Hi Lars. You will need to pad your order data with dummy orders representing each of the synonyms. You can leverage those dummy orders to find the matching category via the LOD search but still exclude them from the results. I hinted at this in the comments in the original LOD expression.

               

              To pad your order data you can use Custom SQL which may be intimidating at first but for this purpose is straightforward. One challenge is that the new Excel connector doesn't support Custom SQL and the legacy Excel connector doesn't support LOD expressions. If you're working with a database you won't have these problems. For the attached TWBX (Synonyms B) I used the legacy Excel connector (Windows only) to make the join, then converted that to Custom SQL, then added the data padding:

               

              SELECT [orders$].[item] AS [item],
                [orders$].[order] AS [order],
                [synonyms$].[category] AS [category],
                [synonyms$].[item] AS [item (synonyms)]
              FROM [orders$]
                LEFT JOIN [synonyms$] ON [orders$].[item] = [synonyms$].[item]
              
              UNION ALL
              
              SELECT [synonyms$].[item] AS [item],
                NULL AS [order],
                [synonyms$].[category] AS [category],
                [synonyms$].[item] AS [item (synonyms)]
              FROM [synonyms$]
              

               

              This adds one dummy order for every item in your synonyms list. Then I extracted the Excel data into a data extract so I could use the same original LOD expression as before. Now you can search for a synonym that wasn't ordered:

              Synonyms B.png

               

              To be able to search for the category name itself (e.g. condiment) you could either add it as a synonym for itself in the synonyms table or extend the Custom SQL above with one additional UNION ALL to add more dummy orders.

               

              I think your biggest remaining challenge will be that you're parsing unstructured text rather than structured orders and I don't know how you're going to categorize "I had a fanta with my cheeseburger." That may require true text pre-processing to split every order into it's keywords that may match your synonyms table. I think that is beyond the scope of Tableau.

               

              Regards,

              Zach