topic Re: Calculating NetWorkDays from 2 tables in QlikView

Calculating NetWorkDays from 2 tables

Mon, 18 Aug 2014 19:33:32 GMT

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)

Re: Calculating NetWorkDays from 2 tables

MK_QSL — Mon, 18 Aug 2014 20:14:13 GMT

Provide sample data file please...

Re: Calculating NetWorkDays from 2 tables

JonnyPoole — Mon, 18 Aug 2014 20:28:25 GMT

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 } >}

Re: Calculating NetWorkDays from 2 tables

jagan — Tue, 19 Aug 2014 02:57:25 GMT

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.

Re: Calculating NetWorkDays from 2 tables

Tue, 19 Aug 2014 17:24:44 GMT

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;

Re: Calculating NetWorkDays from 2 tables

jagan — Wed, 20 Aug 2014 01:29:37 GMT

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.