There's no reason why you shouldn't be able to join the tables. Normally you can do this with the multiple tables option (http://kb.tableausoftware.com/articles/knowledgebase/joining-excel-worksheets), but as you have custom SQL, you could probably take the SQL generated by a simple join and change it to accommodate your other custom stuff.
Thanks, but the functionality you described is for doing joins across tables in the excel worksheets. i need to combine across the datasources.
basically i need to pull the product category field over rather than do the blend. hopefully this makes sense.
Same method - you'll be joining within the custom SQL. Difficult to know exactly what the syntax will be without seeing what you have but it will be along the lines of:
SELECT [Dimension A].[Store] AS [Store],
[Dimension A].[Product] AS [Product],
[Dimension B].[Category] AS [Category]
FROM [Dimension A]
LEFT JOIN [Dimension B] ON [Dimension A].[Product]= [Dimension B].[Product]
That's still JET SQL so there may be some tweaks needed. Others may chime in, but without seeing your custom SQL it will be difficult to give a full answer.
JOINS on SQL:
You are trying to join a database source (Datasource A) and an excel workbook (datasource B), right?
You can do this by joining Datasource A with DataSource B as follows
And once you can go the sheet and add the necessary dimensions and measures. You can also hide all unused fields if you don't want to see the keys joining two datasources. Also are you blending or joining?
I am not sure if this is what you are looking for.