1 Reply Latest reply on Jun 8, 2016 6:37 AM by lenaic.ridinger

    navision table joins in Tableau

    Jason Kleinhans

      Good day

      I am using Tableau 9.3 and connect directly to my Navision data source (SQL) and I use 3-4 tables to Join.

      If you familiar with Microsoft Dynamics Navision it will be of great help for me.

       

      I am doing sales actuals for customers per brand and I am joining the following tables

       

      Customer

      Item Ledger Entry

      Sales Invoice Line

      Value entry

      Item charge

       

      Could you please advise the best column joins for these tables. According to one source , he recons that before data gets to Tableau it loses data , I don't know if he is correct. he also mentioned that I should validate my tables , (I don't know what he means by that).

       

      Here is my Custom SQL script:

      SELECT

       

        [Spier Live$Sales Invoice Header].[Sell-to Customer No_] AS [Sell-to Customer No_],

        [Spier Live$Sales Invoice Header].[Sell-to Customer Name] AS [Sell-to Customer Name],

       

        [Spier Live$Value Entry].[Posting Date] AS [Posting Date (Spier Live$Value Entry)],

        [Spier Live$Value Entry].[Valued Quantity] AS [Valued Quantity],

        [Spier Live$Value Entry].[Invoiced Quantity] AS [Invoiced Quantity],

        [Spier Live$Value Entry].[Cost per Unit] AS [Cost per Unit],

        [Spier Live$Value Entry].[Sales Amount (Actual)] AS [Sales Amount (Actual)],

        [Spier Live$Value Entry].[Discount Amount] AS [Discount Amount],

        [Spier Live$Value Entry].[Global Dimension 1 Code] AS [Global Dimension 1 Code],

        [Spier Live$Value Entry].[Item Charge No_] AS [Item Charge No_],

        [Spier Live$Value Entry].[Cost Amount (Actual)] AS [Cost Amount (Actual)],

        [Spier Live$Value Entry].[Cost Amount (Expected)] AS [Cost Amount (Expected)],

        [Spier Live$Value Entry].[Sales Amount (Expected)] AS [Sales Amount (Expected)],

        [Spier Live$Value Entry].[Expected Cost] AS [Expected Cost],

        [Spier Live$Value Entry].[Inventory Posting Group] AS [Inventory Posting Group],

        [Spier Live$Value Entry].[Gen_ Prod_ Posting Group] AS [All Product Posting Group],

        [Spier Live$Value Entry].[Item No_] AS [Item No_],

       

        [Spier Live$Customer].[City] AS [City],

        [Spier Live$Customer].[Country_Region Code] AS [Country_Region Code],

        [Spier Live$Customer].[Salesperson Code] AS [Salesperson Code (Spier Live$Customer)]

       

       

      FROM [dbo].[Spier Live$Sales Invoice Header] [Spier Live$Sales Invoice Header]

        LEFT JOIN [dbo].[Spier Live$Value Entry] [Spier Live$Value Entry] ON ([Spier Live$Sales Invoice Header].[No_] = [Spier Live$Value Entry].[Document No_])

        

        LEFT JOIN [dbo].[Spier Live$Item Charge] [Spier Live$Item Charge] ON ([Spier Live$Item Charge].[No_] = [Spier Live$Value Entry].[Item Charge No_])

        LEFT JOIN [dbo].[Spier Live$Customer] [Spier Live$Customer] ON ([Spier Live$Sales Invoice Header].[Sell-to Customer No_] = [Spier Live$Customer].[No_])