8 Replies Latest reply on Aug 8, 2012 3:51 PM by Andrew Watson

    Using a dimension from a different data source to filter current data source

    hub.yoo

      Hello Tableau (not the sur le kind), *bad joke*

       

      I have two questions. Well one question but question 2 is a joke to maybe make it so i get more responses...

       

      Question 1

      I have two data sets, key feature of set 1 is that it has order date and ship date, and set 2 is that it has inventory type (key stock/ standing order). Im using set 1 to show the on time shipping over time. I need to use the inventory type filter in set 2 because I want only key stock or standing orders. When applying the filter i get the following:

      Sheet 2.jpg

      Can someone explain an alternative method of linking data sources? I had to put the order_no on the level of detail field so it links the views. Changing this to an attribute makes the viz worse:

       

      Sheet 3.jpg

       

      Question 2

      What do you call a data analyst that uses tableau to create probability dashboards? *going to be a bad joke*

       

      Attached is the packaged tableau workbook as well

        • 1. Re: Using a dimension from a different data source to filter current data source
          Andrew Watson

          Hi, I'm actually going to answer the question in the title rather than 1 or 2. As far as I know the only way to filter a datasource using a dimension from a different datasource is if  the joining field is displayed on the visualisation. I don't think the Level of Detail shelf is interpreted by Tableau as being on the visualisation but I could be wrong.

           

          I only have limited experience joining data sources as I find it frustrating, I almost always end up doing the join using custom SQL at the data connection level or do the join before the data is even put into Tableau and I'd recommend you do the same if possible in this case.

           

          Andrew

          1 of 1 people found this helpful
          • 2. Re: Using a dimension from a different data source to filter current data source
            hub.yoo

            Hello, Thanks for the reply Andrew.

             

            I put it off for too long but looking into custom SQLs now... do you know of a source where I can learn about data manipulation before the data is put into tableau?

             

            The answer to the joke was... "the vizard of odds"

             

            I told you it would be bad

            • 3. Re: Using a dimension from a different data source to filter current data source
              hub.yoo

              Or can you show me how to combine the two? All i want is to link the ship_date to the order_NO in the customerspecialinventory datasource...

               

              SELECT [CustomerSpecialInventory].[Warehouse] AS [Warehouse],

                 [CustomerSpecialInventory].[Part_No] AS [Part_No],

                [CustomerSpecialInventory].[Description_1] AS [Description_1],

                [CustomerSpecialInventory].[Description_2] AS [Description_2],

                [CustomerSpecialInventory].[Qty] AS [Qty],

                [CustomerSpecialInventory].[Expires] AS [Expires],

                [CustomerSpecialInventory].[Min_Qty] AS [Min_Qty],

                [vwExtSOEH].[Order_No] AS [Order_No],

                [vwExtSOEH].[Company_No] AS [Company_No],

                [vwExtSOEH].[Order_Date] AS [Order_Date],

                [vwExtSOEH].[Completed] AS [Completed],

                [vwExtSOEH].[RA_No] AS [RA_No],

                [vwExtSOEH].[Ref_No] AS [Ref_No],

                [vwExtSOED].[Line_No] AS [Line_No],

                [vwExtSOED].[Line_Item_Category] AS [Line_Item_Category],

                [vwExtSOED].[Auto_Discount] AS [Auto_Discount],

                [vwExtSOED].[Item_ID] AS [Item_ID],

                [vwExtSOED].[Description1] AS [Description1],

                [vwExtSOED].[Description2] AS [Description2],

                [vwExtSOED].[Qty_Ordered] AS [Qty_Ordered],

                [vwExtSOED].[Qty_Shipped] AS [Qty_Shipped],

                [vwExtSOED].[Qty_Returned] AS [Qty_Returned],

                [vwExtSOED].[Qty_To_Be_Shipped] AS [Qty_To_Be_Shipped],

                [vwExtSOED].[Qty_To_Be_Returned] AS [Qty_To_Be_Returned],

                [vwExtSOED].[UOM] AS [UOM],

                [vwExtSOED].[Reg_Price] AS [Reg_Price],

                [vwExtSOED].[Break_Price] AS [Break_Price],

                [vwExtSOED].[Unit_Price] AS [Unit_Price],

                [vwExtSOED].[Net_Price] AS [Net_Price],

                [vwExtSOED].[Discount_Pct] AS [vwExtSOED_Discount_Pct],

                [vwExtSOED].[Distributor_Price] AS [Distributor_Price],

                [vwExtSOED].[List_Price] AS [List_Price],

                [vwExtSOED].[Discount_Amount] AS [vwExtSOED_Discount_Amount],

                [vwExtSOED].[Extended_Price] AS [Extended_Price],

                [vwExtSOED].[Break_Discount] AS [vwExtSOED_Break_Discount],

                [vwExtSOED].[Break_Discount_Method] AS [Break_Discount_Method],

                [vwExtSOED].[Break_Discount_Code] AS [Break_Discount_Code],

                [vwExtSOED].[Manual_Price_Change] AS [Manual_Price_Change],

                [vwExtSOED].[Unit_Cost] AS [Unit_Cost],

                [vwExtSOED].[Additional_Cost] AS [Additional_Cost],

                [vwExtSOED].[Promo_Cost] AS [Promo_Cost],

                [vwExtSOED].[PriceID] AS [PriceID],

                [vwExtSOED].[Contract_Price_Code] AS [vwExtSOED_Contract_Price_Code],

                [vwExtSOED].[Service_Exchange_Rate] AS [Service_Exchange_Rate],

                [vwExtSOED].[Promise_Date] AS [Promise_Date],

                [vwExtSOED].[Revised_Date] AS [Revised_Date],

                [vwExtSOED].[ProductID] AS [ProductID],

                [vwExtSOED].[Service_ID] AS [Service_ID],

                [vwExtSOED].[Item_Type] AS [Item_Type],

                [vwExtSOED].[Split] AS [Split],

                [vwExtSOED].[Custom] AS [Custom],

                [vwExtSOED].[Sales_Category] AS [Sales_Category],

                [vwExtSOED].[Salesperson_No] AS [vwExtSOED_Salesperson_No],

                [vwExtSOED].[Cost_Center_ID] AS [Cost_Center_ID],

                [vwExtSOED].[Batch_No] AS [Batch_No],

                [vwExtSOED].[LastUpdate] AS [vwExtSOED_LastUpdate],

                [vwExtSOED].[DateAdded] AS [vwExtSOED_DateAdded],

                [vwExtSOED].[LastUser] AS [vwExtSOED_LastUser]

              FROM [dbo].[CustomerSpecialInventory] [CustomerSpecialInventory]

                LEFT JOIN [dbo].[vwExtSOEH] [vwExtSOEH] ON ([CustomerSpecialInventory].[Ship_To_No] = [vwExtSOEH].[Ship_To_No])

                LEFT JOIN [dbo].[vwExtSOED] [vwExtSOED] ON (([vwExtSOEH].[Order_No] = [vwExtSOED].[Order_No]) AND ([CustomerSpecialInventory].[Part_No] = [vwExtSOED].[Item_ID]))

               

               

               

              and a different data source is

               

              SELECT [vwExtCustomerServiceLevelStar].[Sales_Channel] AS [Sales_Channel],

                [vwExtCustomerServiceLevelStar].[Order_Date] AS [Order_Date],

                [vwExtCustomerServiceLevelStar].[Ship_Date] AS [Ship_Date],

                [vwExtCustomerServiceLevelStar].[Invoice_Date] AS [Invoice_Date],

                [vwExtCustomerServiceLevelStar].[DaysOld] AS [DaysOld],

                [vwExtCustomerServiceLevelStar].[Category] AS [Category],

                [vwExtCustomerServiceLevelStar].[Company_No] AS [Company_No],

                [vwExtCustomerServiceLevelStar].[Company] AS [Company],

                [vwExtCustomerServiceLevelStar].[Order_No] AS [Order_No],

                [vwExtCustomerServiceLevelStar].[Line_no] AS [Line_no],

                [vwExtCustomerServiceLevelStar].[Order_Category] AS [Order_Category],

                [vwExtCustomerServiceLevelStar].[Invoice_No] AS [Invoice_No],

                [vwExtCustomerServiceLevelStar].[Satisfied_Date] AS [Satisfied_Date],

                [vwExtCustomerServiceLevelStar].[Part_No] AS [Part_No],

                [vwExtCustomerServiceLevelStar].[Serial_No] AS [Serial_No],

                [vwExtCustomerServiceLevelStar].[Technique] AS [Technique],

                [vwExtCustomerServiceLevelStar].[Supplier] AS [Supplier],

                [vwExtCustomerServiceLevelStar].[Supply_Channel] AS [Supply_Channel],

                [vwExtCustomerServiceLevelStar].[Qty_Ordered] AS [Qty_Ordered],

                [vwExtCustomerServiceLevelStar].[Qty_Shipped] AS [Qty_Shipped]

              FROM [dbo].[vwExtCustomerServiceLevelStar] [vwExtCustomerServiceLevelStar]

              • 4. Re: Using a dimension from a different data source to filter current data source
                hub.yoo

                Sorry for posting again and "bumping" my topic but i really thought i would have more replies due to my joke. Either negative comments (most likely) or postive, encouraging comments (very unlikely).

                 

                Oh well.

                • 5. Re: Using a dimension from a different data source to filter current data source
                  Andrew Watson

                  You look familiar with the Custom SQL, you've already done it for the first data source. Can't you just expand that to add the data from the second data source?

                  • 6. Re: Using a dimension from a different data source to filter current data source
                    hub.yoo

                    I don't know.. the first set was done by tableau...

                     

                    how do i add the second part? its a different datasource so I can't just link them right?

                     

                    like for

                     

                    LEFT JOIN [dbo].[vwExtSOED] [vwExtSOED] ON (([vwExtSOEH].[Order_No] = [vwExtSOED].[Order_No]) AND [vwExtCustomerServiceLevelStar].[Sales_Channel] AS [Sales_Channel],

                     

                    I can't join them because its different data sources no?

                    • 7. Re: Using a dimension from a different data source to filter current data source
                      hub.yoo

                      For example, the following gives me an error of untitled.JPG

                       

                      SELECT [tblShippingMasterLog].[RecID] AS [RecID],

                        [tblShippingMasterLog].[InvoiceNo] AS [InvoiceNo],

                        [tblShippingMasterLog].[InvoiceDate] AS [InvoiceDate],

                        [tblShippingMasterLog].[ShipmentDate] AS [ShipmentDate],

                        [tblShippingMasterLog].[ShippingCost] AS [ShippingCost],

                        [tblShippingMasterLog].[ShipToName] AS [ShipToName],

                        [tblShippingMasterLog].[ShipToCity] AS [ShipToCity],

                        [tblShippingMasterLog].[ShipToState] AS [ShipToState],

                        [tblShippingMasterLog].[ShipToCountry] AS [ShipToCountry],

                        [tblShippingMasterLog].[InvTerms] AS [InvTerms],

                        [tblShippingMasterLog].[InvFOB] AS [InvFOB],

                        [tblShippingMasterLog].[DutiesTaxes] AS [DutiesTaxes],

                      FROM [dbo].[tblShippingMasterLog] [tblShippingMasterLog]

                        INNER JOIN [dbo].[CustomerSpecialInventory] [CustomerSpecialInventory] ON ([tblShippingMasterLog].[ShipToName] = [CustomerSpecialInventory].[Ship_To_Name])

                      • 8. Re: Using a dimension from a different data source to filter current data source
                        Andrew Watson

                        I thought you were connecting to a database initially, I didn't know Tableau could build it's own SQL strings with Left Joins etc. Your connection and naming conventions all appear a bit SQL Server-esque. Your error message also implies you're connecting to SQL Server but you have some permissions issue perhaps? It's hard for me to say what the issue is but I would check permissions first of all.