LEFT JOIN

jasonkwp

Hi

I am using Microsoft Dynamics NAV 2013 , SQL server 2014 , Tableau Desktop

I have created a new table which contains data from the Excise Rate, Location , Item and Item Unit of Measure tables in order to calculate Transfer Excise. Transfer Excise is Tax that we pay when our wine gets transported from one Warehouse to another. I did a Left Join to the Value Entry table which contains all item related transactions as I need the Invoice Quantity column to calculate Transfer Excise. This is how I calculate Transfer Excise: (Invoiced Quantity / Litre Conversion Factor * Unit Rate) = Transfer Excise. With this approach I did get the desired result , see the SQL script to get the Transfer Excise

SELECT DISTINCT b.[Entry No_] ,
a.[Starting Date],
b.[Posting Date],
b.[Item No_],
b.[Invoiced Quantity],
b.[Sales Amount (Actual)],
a.[Litre Conversion Factor],
a.[Unit Rate] ,
b.[Location Code],
a.[Excise Location],
a.[Excise Type Code],
a.[Unit Of Measure Code]
FROM [Spier Live$Value Entry] b
LEFT JOIN [Transfer Excise Tbl] a
ON a.[No_] = b.[Item No_]
AND b.[Location Code] = a.[Location Code]
AND DateDiff(y,b.[Posting Date],a.[Starting Date]) > -365
AND DateDiff(y,b.[Posting Date],a.[Starting Date]) < 0
WHERE b.[Posting Date] > '2013-02-26'
AND b.[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA')
AND b.[Invoiced Quantity] <>0

GROUP BY b.[Entry No_] ,a.[Starting Date], b.[Posting Date], b.[Item No_], b.[Invoiced Quantity],
b.[Sales Amount (Actual)], a.[Litre Conversion Factor], a.[Unit Rate] ,
b.[Location Code], a.[Excise Location], a.[Excise Type Code], a.[Unit Of Measure Code]


With all this done , the sales amount (Actual) value changes which should not be the case. Someone told me that it might be the Left Join causing the Sales Amount(Actual) to change. I cannot change the script because then the Transfer Excise Value will be incorrect or is there another or better way of writing this query so that the sales amount (Actual) and Transfer Excise values will remain the same. Oh the reason for the way i wrote the query is because i want to get the Unit Rate for each item transaction.

Peter+is1

Hi,

Although the SQL statement is odd, it could not cause a wrong representation of Sales Amount (Actual).
Perhaps Tableau does something to the data before display?

You could test your SQL in SQL Server Management Studio.

good luck.

vaprog

Should't you use grouping and SUM on Sales Amount (Actual) and Invoiced Quantity rather than using DISTINCT?

jasonkwp

I have only selected the Sales Amount(Actual) to test with that same query and I summed the Sales Amount(Actual) and I get an incorrect Value , in my olap cube report my Sales Amount(Actual) should be 32,145,105.66 , for the month of December 2015. The reason I am using history data is to test my values so that I don't have to worry about live data all the time. So applying this query I do get my Transfer Excise amount I need but somehow is affects the Sales Amount(Actual) value and I don't know what it might be

jasonkwp

evens if I do use group and sum , I still don't get the desired result , so I cannot be Tableau

Peter+is1

Group and Sum do not fix missing data...

My final thought, could the filter [WHERE b.[Posting Date] > '2013-02-26'
AND b.[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA')
AND b.[Invoiced Quantity] <>0]
be different in your other Sales Amount(Actual) report/query/cube?

I am sorry you cannot test in SQL server management studio. That would make things a lot easier.

Good luck.

jasonkwp

the filters is not for Sales Amount(Actual) the Sales Amount(Actual) is the amount of all sales transactions in the value entry table in Navision. The [WHERE b.[Posting Date] > '2013-02-26'
AND b.[Gen_ Bus_ Posting Group] IN ('LOCA','EXSA')
AND b.[Invoiced Quantity] <>0] Filters was done to get the correct data from my Transfer Excise table to calculate Transfer Excise. If the filters is the problem what better way is there so that the Left Join to the Transfer Excise Tbl and the filters does not affect the Sales Amount (Actual) value

Peter+is1

Please read the suggestions carefully.
It seems like you are sometimes jumping to conclusions.

The filter is 100% on table b, the value entry.

Good luck.

jasonkwp

but you know what I am trying to say , do you guys have any suggestions, If you know Navision that cool that will be a plus

MJV

Your distinct in the select clause is assuming that none of those records potentially being selected is duplicate in all fields.

If any of them are, they are being excluded.

If all amounts are >0, then you should get less than when you just sum across the specified dates.

If amounts can be less than zero, then you may get more or less or equal to the amount you get when you just sum across the dates.

In other words, unless the field list you are using to select from represents a primary key, you are probably leaving out some of the records due to the distinct clause.

have you tried this statement without the distinct clause?

Michael

jasonkwp

This is my original SQL query and the Sales Amount (Actual) is 32,145,105.66

, this is without the Left Join to the Transfer Excise Tbl. When I add the left join to the Transfer Excise Tbl then I get a much higher value of [b]229,737,410[/b]

SELECT 
  [Spier Live$Value Entry].[Item No_] AS [Item No_],
  [Spier Live$Value Entry].[Posting Date] AS [Posting Date],
  [Spier Live$Value Entry].[Inventory Posting Group] AS [Inventory Posting Group],
  [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 [Brand Class Code],
  [Spier Live$Value Entry].[Cost Amount (Actual)] AS [Cost Amount (Actual)],
  [Spier Live$Value Entry].[Expected Cost] AS [Expected Cost],
  [Spier Live$Value Entry].[Item Charge No_] AS [Item Charge No_],
  [Spier Live$Value Entry].[Sales Amount (Expected)] AS [Sales Amount (Expected)],
  [Spier Live$Value Entry].[Cost Amount (Expected)] AS [Cost Amount (Expected)],
  [Spier Live$Value Entry].[Variant Code] AS [Variant Code],
  [Spier Live$Value Entry].[Document No_] AS [Document No_],
  [Spier Live$Value Entry].[Gen_ Prod_ Posting Group] AS [Gen_ Prod_ Posting Group],
  [Sales Invoice_CrMemo Tbl].[Posting Group] AS [Posting Group],
  [Sales Invoice_CrMemo Tbl].[Shipment Date] AS [Shipment Date],
  [Spier Live$Customer].[No_] AS [Customer No],
  [Spier Live$Customer].[Name] AS [ Customer Name],

 
  
FROM [dbo].[Spier Live$Value Entry] [Spier Live$Value Entry]
  RIGHT JOIN [dbo].[Sales Invoice_CrMemo Tbl]
  ON (([Spier Live$Value Entry].[Document No_] = [Sales Invoice_CrMemo Tbl].[Document No_]) 
  AND ([Spier Live$Value Entry].[Item No_] = [Sales Invoice_CrMemo Tbl].[No_]) 
  AND ([Spier Live$Value Entry].[Source No_] = [Sales Invoice_CrMemo Tbl].[Sell-to Customer No_]))
  
  LEFT JOIN [dbo].[Spier Live$Item Ledger Entry] [Spier Live$Item Ledger Entry] 
  ON (([Sales Invoice_CrMemo Tbl].[No_] = [Spier Live$Item Ledger Entry].[Item No_]) 
  AND ([Sales Invoice_CrMemo Tbl].[Sell-to Customer No_] = [Spier Live$Item Ledger Entry].[Source No_]) 
  AND ([Sales Invoice_CrMemo Tbl].[Document No_] = [Spier Live$Item Ledger Entry].[Document No_]))

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

                        

GROUP BY [Spier Live$Value Entry].[Item No_],[Spier Live$Value Entry].[Posting Date],
         [Spier Live$Value Entry].[Inventory Posting Group], [Spier Live$Value Entry].[Invoiced Quantity],
		 [Spier Live$Value Entry].[Cost per Unit], [Spier Live$Value Entry].[Sales Amount (Actual)],
		 [Spier Live$Value Entry].[Discount Amount], [Spier Live$Value Entry].[Global Dimension 1 Code],
		 [Spier Live$Value Entry].[Cost Amount (Actual)], [Spier Live$Value Entry].[Expected Cost],
		 [Spier Live$Value Entry].[Item Charge No_],[Spier Live$Value Entry].[Sales Amount (Expected)],
		 [Spier Live$Value Entry].[Cost Amount (Expected)],[Spier Live$Value Entry].[Variant Code],
		 [Sales Invoice_CrMemo Tbl].[Posting Group],[Sales Invoice_CrMemo Tbl].[Shipment Date],
		 [Spier Live$Customer].[No_], [Spier Live$Customer].[Name],[Spier Live$Value Entry].[Document No_],
         [Spier Live$Value Entry].[Gen_ Prod_ Posting Group]
       

vaprog

jasonkwp wrote: »

When I add the left join to the Transfer Excise Tbl then I get a much higher value of 229,737,410

This means, the mapping from a to b is not 1:1 which it probably should be.

If the mapping is not 1:1, you cannot sum Sales Amount (Actual) in the result and expect it to be the correct Sales Amount (Actual).

If it shold be 1:1, check the join condition (it's likely the values in a.[Starting Date] which cause you trouble, then).