1 of 1 people found this helpful
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.
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).
3. Create a calculated field using an LOD expression to figure out if an order's category matches the search item's category
4. Put it all together on a dashboard for testing
I've attached an Excel sheet with the test data and a Tableau 9.3 TWBX.
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!!
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:
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.
Synonyms B.twbx 15.2 KB