Calculating NetWorkDays from 2 tables

[#1]

I have a date in a Detail file that shows when a part was received.  I have a date in a Header file that shows when that part was due.  I am calculating the NetWorkDays between these two dates.  I currently use NetWorkDays in a Straight Table Chart and it works great because I have loaded in the Detail file first and Left Joined the Header file in second.

I want to utilize the amount of NetWorkDays in Set Analysis, but I cannot get the Set Analysis to understand the NetWorkDays formula.  I keep getting an error.

So, how do I set-up the NetWorkDays as a new value in the script?  Once it is in the script- call it NetWorkDaysValue, I could easity use it in Set Analysis.  But, since they are in two seperate file, I cannot seem to figure out how to create this new value.

Maybe there is a way to perform a NetWorkDays function in the script that I am missing.  Need your help.

Here is my failed formula: Sum({$<NetWorkDays(RequiredDate,ReceiptDate) < {4}>}QuantityReceived)

[MK_QSL]

Provide sample data file please...

[JonnyPoole]

Are you just trying to filter out rows from the table where Networkdays < 4 ? 

how about:  sum( if( NetWorkDays(RequiredDate,ReceiptDate) < 4, QuantityRecieved,0)) ?

Otherwise for Set Analysis, its ok to use functions like networkdays , but you need use a field on the left side of the set modifier.    Sum( {$< FieldName={ theexperssion } >}

[jagan]

Hi,

Try like this

DetailTemp:

LOAD
*

FROM Details;

LEFT JOIN(DetailTemp)

LOAD

*

FROM Header;

Detail:

LOAD

*,

NetWorkDays(RequiredDate, ReceiptDate) AS NetWorkDaysValue

FROM DetailTemp;

DROP TABLE DetailTemp;

Regards,

Jagan.

[#5]

Jagan-

I think I got close using your idea, but it just wouldn't take the last bit of code.  Here is my script with two minor tweaks and then your code that I added to my script.  Please forgive me as I am not a programmer and have only learned QlikView by watching YouTube and by trial/error. I added the "DetailTemp:" at the beginning and changed the Left Join table name inside the brackets as seen below. It kept telling me it couldn't find the Excel file out on our network or something like that. I removed your script too soon, so I can't give you the exact message from QV.  Can you help me fix this?

CONNECT32 TO [******************] (removed for security reasons);

DetailTemp:

LOAD PurchaseOrderNo, ReceiptType, ReceiptNo, HeaderSeqNo, ItemCode, RequiredDate,
LineSeqNo, ItemCodeDesc, UnitOfMeasure, LineKey,
QuantityReceived, ExtensionAmt

WHERE ItemCode <> '/C';

SQL SELECT *
FROM "PO_ReceiptHistoryDetail";

Left Join (DetailTemp)
LOAD PurchaseOrderNo, ReceiptType, ReceiptNo, HeaderSeqNo, ReceiptDate,
Month(ReceiptDate) as ReceiptMonth, day(ReceiptDate) as ReceiptDay, year(ReceiptDate) as ReceiptYear,
VendorNo, PurchaseName;

SQL SELECT *
FROM "PO_ReceiptHistoryHeader";

Miy code above:

Your code below:

Detail:

LOAD

*,

NetWorkDays(RequiredDate, ReceiptDate) AS NetWorkDaysValue

FROM DetailTemp;

DROP TABLE DetailTemp;

[jagan]

Hi,

Try this code

CONNECT32 TO [******************] (removed for security reasons);

DetailTemp:

LOAD PurchaseOrderNo, ReceiptType, ReceiptNo, HeaderSeqNo, ItemCode, RequiredDate,
LineSeqNo, ItemCodeDesc, UnitOfMeasure, LineKey,
QuantityReceived, ExtensionAmt
WHERE ItemCode <> '/C';
SQL SELECT *
FROM "PO_ReceiptHistoryDetail";

Left Join (DetailTemp)
LOAD PurchaseOrderNo, ReceiptType, ReceiptNo, HeaderSeqNo, ReceiptDate,
Month(ReceiptDate) as ReceiptMonth, day(ReceiptDate) as ReceiptDay, year(ReceiptDate) as ReceiptYear,
VendorNo, PurchaseName;
SQL SELECT *
FROM "PO_ReceiptHistoryHeader";

Detail:

LOAD

*,

NetWorkDays(RequiredDate, ReceiptDate) AS NetWorkDaysValue

FROM DetailTemp;

DROP TABLE DetailTemp;

Hope this helps you.

Regards,

Jagan.