Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
nburton78
New Contributor III

Using a formula from 2 tables joined

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
3 Replies
pooja_prabhu_n
Contributor III

Re: Using a formula from 2 tables joined

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
New Contributor III

Re: Using a formula from 2 tables joined

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
Valued Contributor

Re: Using a formula from 2 tables joined

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

Dummy data:


Data from spreadsheet

Order NumberLine NumberOrder TypeLot NumberActual Ship DatePromised Ship Date
11Cont12322/09/201820/09/2018
21Cont45623/09/201827/09/2018
31Cont78924/09/201820/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:

Ship.PNG

With CheckDate as [Promised Ship minus Last receipt]



Hope this helps.