If you have flexibility in restructuring the data table, I would suggest converting the five product columns into 2 columns: say Product Number (with values from 1 to 5) and Product ID or Name (the 4000+ names or IDs of the products). Then you can set up a Quick Filter on the Product ID/Name field to get the appropriate orders. If your data source is Excel or text-based you could use the Pivot functionality in Tableau 9 to do this.
An alternative approach might be to use a parameter to search across the 5 fields. For instance, see the attached workbook. I have used the "Sample Superstore" data set to illustrate. I created a Search Name Parameter and created a calculated field called "Found" with the following formula, so the user can enter a search string that finds a match in either Ship Mode, Category or Sub-Category fields. You could use the CONTAINS function for partial matching. I then added this field to the filter pane, and set it to show only the records with value 1.
IF ([Ship Mode] = [Search Name]) OR ([Category]=[Search Name]) OR ([Sub-Category]=[Search Name]) THEN 1 ELSE 0 END
In your case, you would use [product_1] = [Search Name], [product_2]=[Search Name], etc. in the IF condition.
Hope this helps.
ParameterSearch.twbx 1.2 MB
Hi Shine --
Many thanks for your response. Got trapped in other items this week so have not had a chance to review your suggestions. Will do so shortly and get back on the forum with my observations.