Using a formula from 2 tables joined

[nburton78]

I have data coming from 2 SQL's.  Table A and Table B they join  and fields from both A and B are used in the formula for LastReceiptDate.  My question is how do I write it so I can use LastReceiptDate in a formula.  For example, Promised Ship Date from Table A minus LastReceiptDate.  I may have my code written incorrectly.  but everytime I try to add the formulas I get nothing but Script errors  Below is a simplified version of my script:

Table A:

order number

line number

order type

lot number

Actual ship date

promised ship date from....

Left join table A

lot number

lot creation date from.....

Left Join table A

load

order number

order type

line number

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

resident table A

Group By Order_Number, Order_Type, Line_Number

[pooja_prabhu_n]

Hi,

There is no Creation_Date__ILCRDJ and Actual_Ship_Date__SDADDJ date columns in Table A.

and in group by you are using Order_Number, Order_Type, Line_Number,instead of that use

Group By [order number], [order type], [line number].

What is the script error you are getting?

If possible can you share some sample app.

Thanks,

Pooja

[nburton78]

Sorry that was just a stupid typo.  This is a simplified version of the script so field names are not necessarily accurate.  I have updated the script to show what it says right now.  This script works and returns results.

Table A:

order number

line number

order type

lot number

Actual ship date

promised ship date from....

Left join table A

lot number

lot creation date from.....

Left Join table A

load

order number

order type

line number

Max(if(lot creation date<=Actual ship date,lot creation date)) AS LastReceiptDate

resident table A

Group By Order_Number, Order_Type, Line_Number

If I add the line "promised ship date-LastReceiptDate as [Promised Ship minus Last receipt]" I get a script error saying something about cant find last receipt date.  If I try "promised ship date-Max(if(lot creation date<=Actual ship date,lot creation date)) as [Promised Ship minus Last receipt]".  I get an error about the promised ship being missing  I add either of these lines directly below "Max(if(lot creation date<=Actual ship date,lot creation date)) AS LastReceiptDate" ,  Am I joining these tables incorrectly?

[timpoismans]

I tried the following, all with dummy data I just threw together:

Dummy data:

Data from spreadsheet

Order Number	Line Number	Order Type	Lot Number	Actual Ship Date	Promised Ship Date
1	1	Cont	123	22/09/2018	20/09/2018
2	1	Cont	456	23/09/2018	27/09/2018
3	1	Cont	789	24/09/2018	20/10/2018

Inline load (see script)

Lot Number, Lot Creation Date

    123, 10/09/2018

    456, 10/09/2018

    789, 10/09/2018

Script:

Ship:

LOAD

    "Order Number",

    "Line Number",

    "Order Type",

    "Lot Number",

    "Actual Ship Date",

    "Promised Ship Date"

FROM [lib://Community/Order.xlsx]

(ooxml, embedded labels, table is Blad1);

Left Join(Ship)

Load * Inline [

Lot Number, Lot Creation Date

    123, 10/09/2018

    456, 10/09/2018

    789, 10/09/2018

];

Left Join(Ship)

Load

"Order Number",

    "Line Number",

    "Order Type",

    Date(Max(if([Lot Creation Date]<="Actual Ship Date",[Lot Creation Date]))) AS LastReceiptDate

Resident Ship

Group By "Order Number","Line Number","Order Type";

FinalShip:

NoConcatenate

Load

*,

    "Promised Ship Date" - LastReceiptDate AS CheckDate

Resident Ship;

Drop Table Ship;

And I get the following result:

With CheckDate as [Promised Ship minus Last receipt]

Hope this helps.