topic Re: Networkdays Issue in QlikView

Networkdays Issue

pipuindia99 — Tue, 18 Jun 2019 06:23:37 GMT

I am facing some issues with Networkdays function when i tried passing the variable.

Please see the below script which i have used and also the error which i am getting.

Please let me know if i miss anything or guide me what to do to get that value/

T:
LOAD * Inline
[Indate, Outdate, %SalesOrg
6/5/2019, 6/7/2019, 1100];

Holiday:
LOAD Slno,
Country,
date(Holiday_Date) as Public_Holiday_Date,
SalesOrg as %SalesOrg
FROM
[..\..\Holiday list 2019_US_EU.xlsx]
(ooxml, embedded labels, table is Sheet1);

Holiday_List_1:
load
concat(chr(39) & Public_Holiday_Date & chr(39),',') as ListOfHolidays_date,
%SalesOrg
Resident Holiday Group By %SalesOrg;

DROP Table Holiday;

LET vListOfHolidays = '=only(ListOfHolidays_date)';

T1:
LOAD *,
NetWorkDays(Indate,Outdate,$(vListOfHolidays)) as Netdate
Resident T;

DROP Table T;

EXIT SCRIPT;

Re: Networkdays Issue

sunny_talwar — Tue, 18 Jun 2019 11:52:16 GMT

Try changing

LET vListOfHolidays = '=only(ListOfHolidays_date)';

LET vListOfHolidays = Peek('ListOfHolidays_date');

Re: Networkdays Issue

sunilpaul — Tue, 18 Jun 2019 11:58:50 GMT

Instead of LET in your expression, try to use SET

Regards,

Re: Networkdays Issue

pipuindia99 — Wed, 19 Jun 2019 08:43:56 GMT

Tried, not working

Re: Networkdays Issue

pipuindia99 — Wed, 19 Jun 2019 08:45:08 GMT

It will choose only the 1st row of that date table. instead it should choose the selected country holiday dates

Re: Networkdays Issue

sunny_talwar — Wed, 19 Jun 2019 10:30:49 GMT

May be there is a better way to do this.... try this

T: LOAD * Inline [Indate, Outdate, %SalesOrg 6/5/2019, 6/7/2019, 1100]; Holiday: LOAD Slno, Country, date(Holiday_Date) as Public_Holiday_Date, SalesOrg as %SalesOrg FROM [..\..\Holiday list 2019_US_EU.xlsx] (ooxml, embedded labels, table is Sheet1); Left Join (T) LOAD Concat(chr(39) & Public_Holiday_Date & chr(39),',') as ListOfHolidays_date, %SalesOrg Resident Holiday Group By %SalesOrg; DROP Table Holiday; T1: LOAD *, NetWorkDays(Indate, Outdate, ListOfHolidays_date) as Netdate Resident T; DROP Table T; EXIT SCRIPT;

Re: Networkdays Issue

pipuindia99 — Fri, 21 Jun 2019 09:34:23 GMT

It didnt work 😞

T:
LOAD * Inline
[Indate, Outdate, %SalesOrg
6/5/2019, 6/7/2019, 1100
6/4/2019, 6/8/2019, 8000];

Holiday:
LOAD Slno,
Country,
//Num([Holiday_Date]) as Public_Holiday_Date_Num,
date(Holiday_Date) as Public_Holiday_Date,
SalesOrg as %SalesOrg
FROM
[L:\2Integration\QVD\Master data\Holiday list 2019_US_EU.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(T)
Holiday_List:
load
//concat(chr(39) & Public_Holiday_Date_Num & chr(39),',') as ListOfHolidays_Num,
//concat(Public_Holiday_Date,',') as ListOfHolidays,
concat(chr(39) & Public_Holiday_Date & chr(39),',') as ListOfHolidays_date,
%SalesOrg
Resident Holiday Group By %SalesOrg;

DROP Table Holiday;

//LET vListOfHolidays = '=only(ListOfHolidays_date)';

T1:
LOAD *,
NetWorkDays(Indate,Outdate,ListOfHolidays_date) as Netdate
Resident T;

DROP Table T;

EXIT SCRIPT;

Re: Networkdays Issue

sunny_talwar — Fri, 21 Jun 2019 10:26:57 GMT

Would you be able to share Holiday list 2019_US_EU.xlsx so that we can try to run this and test it out?

Re: Networkdays Issue

pipuindia99 — Fri, 21 Jun 2019 10:38:03 GMT

attached

Re: Networkdays Issue

sunny_talwar — Fri, 21 Jun 2019 12:22:15 GMT

Try this

T: LOAD * INLINE [ Indate, Outdate, %SalesOrg 6/5/2019, 6/7/2019, 1100 6/4/2019, 6/8/2019, 8000 ]; FOR i = 1 to FieldValueCount('%SalesOrg') LET vSalesOrg = FieldValue('%SalesOrg', $(i)); Holiday: LOAD Slno, Country, Date(Holiday_Date) as Public_Holiday_Date, SalesOrg as %SalesOrg FROM [..\..\Downloads\Holiday list 2019_US_EU.xlsx] (ooxml, embedded labels, table is Sheet1) Where SalesOrg = '$(vSalesOrg)'; Holiday_List: LOAD Concat(chr(39) & Public_Holiday_Date & chr(39),',') as ListOfHolidays_date Resident Holiday; LET vHolidays = Peek('ListOfHolidays_date'); DROP Tables Holiday, Holiday_List; T1: LOAD *, NetWorkDays(Indate, Outdate, $(vHolidays)) as Netdate Resident T Where %SalesOrg = '$(vSalesOrg)'; NEXT DROP Table T;