Calculated fields from multiple tables

[nburton78]

Please be nice I'm new!  so I have 2 sql load statements in my script.  see examples below

sql select part_number, creation_date

from Table A;

sql select part_number, ship_date

from table B;

Then I have an expression for a graph which calculates the last receipt:

expression- Max(if(creation_date<=ship_date,creation_date))

I would like to write the last receipt calculation into my script but I don't know how to calculate it when it's pulling from 2 different tables.  Please help!

[vishsaggi · Accepted solution]

Do a left join like

TableA:

sql select part_number, creation_date

from Table A;

LEFT JOIN(TableA)

TableB:

sql select part_number, ship_date

from table B;

Left Join(TableA)

FinalTab:

LOAD part_number,

           Max(if(creation_date<=ship_date,creation_date)) AS LastReceiptDate

Resident TableA

Group By part_number;

[nburton78]

thank you! The join works perfectly but now the formula is giving me an incorrect answer.  Any idea why this the formula would work perfectly as an expression but not in the script?  it may have to do with specifying what instance of ship date i am referring to.  If that's the case would this script work if i brought in the order number?  maybe?

[vishsaggi]

Probably yes. I am not sure about the details so if you can provide more information on the script you are trying on or paste your script with the output you are looking for will help me to work on it.

[nburton78]

Here's the script:  There's quite a bit here and like I said, I'm a new user so this may not be the best way to write all this.  Please don't judge me! The end result is that I want the "lastreceiptdate" field to pull the last creation date (in table b) that is less than the actual ship date in (table a). 

TableA:

SQL SELECT      

dbo.V_ItemBranchMaster.IBSTKT__Stocking_Type,

dbo.V_OTD_Outbound_Americas.Header_Business_Unit__SDEMCU,

                      dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU,

                      dbo.V_OTD_Outbound_Americas.Item_Classification_Detail,

                      dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM as [Short],

                      dbo.V_OTD_Outbound_Americas.IMLITM__World_Wide_Item_Number,

                      dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO as [Order_Number],

                      dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO as [Order_Type],

                      dbo.V_OTD_Outbound_Americas.Late_Reason_Code,

                      dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID as [Line_Number],

                      dbo.V_OTD_Outbound_Americas.Bill_To_Address_Number__SDAN8 as [Bill_To],

                      dbo.V_OTD_Outbound_Americas.Bill_To_Customer_Name,

                      dbo.V_OTD_Outbound_Americas.Ship_To_Address_Number__SDSHAN,

                      dbo.V_OTD_Outbound_Americas.Ship_To_Customer_Name,

                      dbo.V_OTD_Outbound_Americas.Customer_Service_Level_Bill_To,

                      dbo.V_OTD_Outbound_Americas.Quantity_Shipped__SDSOQS,

                      dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ,

                      dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ,

                      dbo.V_OTD_Outbound_Americas.IBLTCM__Leadtime_Cumulative,

                      dbo.V_OTD_Outbound_Americas.Requested_Date_SDDRQJ,

                      dbo.V_OTD_Outbound_Americas.Leadtime_Date_at_SLUPMJ_Order_Date,

                      dbo.V_OTD_Outbound_Americas.Scheduled_Pick_Date__SDPDDJ,

                      dbo.V_OTD_Outbound_Americas.PickData_Transmission_Date__SLUPMJ,

                      dbo.V_OTD_Outbound_Americas.PickData_Transmission_Time__SLTDAY,

                      dbo.V_OTD_Outbound_Americas.Promised_Ship_Date__SDPPDJ,

                      dbo.V_OTD_Outbound_Americas.Promised_Delivery_Date__SDRSDJ,

                      dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ,

                      dbo.V_OTD_Outbound_Americas.Requested_On_Time,

                      dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Requested,

                      dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Ship,

                      dbo.V_OTD_Outbound_Americas.Date_Diff_Actual_Ship_to_Promised_Delivery,

                      dbo.V_OTD_Outbound_Americas.Supplier_Name,

                      MONTH(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Month,

                      YEAR(dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ) AS Actual_Ship_Year,

                      dbo.V_Item_Ledger_Cardex.Lot_Number__ILLOTN,

                       case when dbo.V_OTD_Outbound_Americas.Order_Date__SDTRDJ=dbo.V_OTD_Outbound_Americas.Order_Date__SLUPMJ

                       then datediff(dd,Requested_Date_SDDRQJ,Order_Date__SDTRDJ)

                       else datediff(dd,Requested_Date_SDDRQJ,Order_Date__SLUPMJ)

                       end  as [Request date- order date],

                       case when Requested_Date_SDDRQJ<Promised_Ship_Date__SDPPDJ

                       then 'True'

                       else 'False'

                       end as [Request date before PSD],

                       datediff(dd,PickData_Transmission_Date__SLUPMJ,Requested_Date_SDDRQJ ) as [Packslip Print-Req Date],

                       DATEDIFF(dd,Scheduled_Pick_Date__SDPDDJ,Requested_Date_SDDRQJ ) as [Days between scheduled pick and request],

                       datediff(dd,PickData_Transmission_Date__SLUPMJ,Actual_Ship_Date__SDADDJ) as [Days between packslip print and actual ship],

                       DATEDIFF(dd,PickData_Transmission_Date__SLUPMJ,Scheduled_Pick_Date__SDPDDJ) as [Days between pack slip and sched pick date],

                       case when  Line_Number__SDLNID%1000 =0

                       then 'Not a Split Line'

                       else 'Split Line'

                       end as [Split Line],

case when (Leadtime_Date_at_SLUPMJ_Order_Date-Order_Date__SLUPMJ)+Order_Date__SDTRDJ>Scheduled_Pick_Date__SDPDDJ

then  'Order entered inside LT'

else 'Lead Time respected'

end as [LT respected],

Requested_Date_SDDRQJ+8 as [Requested_plus_eight]

FROM         dbo.V_ItemBranchMaster INNER JOIN

                      dbo.V_OTD_Outbound_Americas ON dbo.V_ItemBranchMaster.IBMCU__Business_Unit = dbo.V_OTD_Outbound_Americas.Business_Unit__SDMCU AND

                      dbo.V_ItemBranchMaster.IBITM__Item_Number_Short = dbo.V_OTD_Outbound_Americas.Short_Item_Number__SDITM INNER JOIN

                      dbo.V_Item_Ledger_Cardex ON dbo.V_OTD_Outbound_Americas.Order_Number__SDDOCO = dbo.V_Item_Ledger_Cardex.Order_Number__ILDOCO AND

                      dbo.V_OTD_Outbound_Americas.Order_Type__SDDCTO = dbo.V_Item_Ledger_Cardex.Order_Type__ILDCTO AND

                      dbo.V_OTD_Outbound_Americas.Line_Number__SDLNID = dbo.V_Item_Ledger_Cardex.Order_Line__ILLNID

WHERE     (dbo.V_OTD_Outbound_Americas.Actual_Ship_Date__SDADDJ >= CONVERT(DATETIME, '2013-01-01 00:00:00', 102));

Left Join(TableA)

TableB:

SQL SELECT DISTINCT Lot_Number__ILLOTN, Creation_Date__ILCRDJ

FROM         dbo.V_Item_Ledger_Cardex

WHERE     (Explanation__ILTREX <> 'Landed Cost') AND (Document_Type__ILDCT IN ('OV', 'IC'));

left Join(TableA)

FinalTable:

load Lot_Number__ILLOTN,

Max(if(Creation_Date__ILCRDJ<=Actual_Ship_Date__SDADDJ,Creation_Date__ILCRDJ)) AS LastReceiptDate

Resident TableA

Group By Lot_Number__ILLOTN;

[vishsaggi]

Your script looks absolutely fine. So the tableA leftjoin with TableB gets the LOT Number and CreationDate for those lot numbers. Then we are using the Field LotNumber to group by to get the Max creation date from those dates where creation date is <= ActualShipDate. So you said this expr works in front end so what dimension are you using? If you use LotNumber as dimension and try this expr are you not getting the right result?

[nburton78]

order number and line number, these would have to be added to the join right?  like this??  Table a and table b would join on lot number since its the only field they both share so do I even need the lot number?

for each distinct order number and line number combination , I need to know what the last receipt was prior to shipment. 

Left Join(TableA)

TableB:

SQL SELECT DISTINCT Lot_Number__ILLOTN, Creation_Date__ILCRDJ

FROM         dbo.V_Item_Ledger_Cardex

WHERE     (Explanation__ILTREX <> 'Landed Cost') AND (Document_Type__ILDCT IN ('OV', 'IC'));

left Join(TableA)

FinalTable:

load

Order_Number__SDDOCO ,

Line_Number__SDLNID

Max(if(Creation_Date__ILCRDJ<=Actual_Ship_Date__SDADDJ,Creation_Date__ILCRDJ)) AS LastReceiptDate

Resident TableA

Group By Order_Number__SDDOCO ,Line_Number__SDLNID

[vishsaggi]

This seems convincing. So did you try this code and checked if it is picking right receiptdate?

[nburton78]

I tried, there was an error on the reload.  I will have to keep trying.  thank you

[vishsaggi]

What is the error can you share a snap shot of the error?