topic Re: Using a formula from 2 tables joined in QlikView

Using a formula from 2 tables joined

nburton78 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Using a formula from 2 tables joined

pooja_prabhu_n — Fri, 21 Sep 2018 05:43:10 GMT

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

Re: Using a formula from 2 tables joined

nburton78 — Tue, 25 Sep 2018 12:49:37 GMT

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?

Re: Using a formula from 2 tables joined

timpoismans — Tue, 25 Sep 2018 13:13:21 GMT

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.