Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pipuindia99
Creator III
Creator III

Networkdays Issue

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;

Networkdays.PNG

 

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

9 Replies
sunny_talwar

Try changing

LET vListOfHolidays = '=only(ListOfHolidays_date)';

to

LET vListOfHolidays = Peek('ListOfHolidays_date');
sunilpaul
Contributor II
Contributor II

Instead of LET in your expression, try to use SET

Regards,
pipuindia99
Creator III
Creator III
Author

Tried, not working

pipuindia99
Creator III
Creator III
Author

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

sunny_talwar

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;
pipuindia99
Creator III
Creator III
Author

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;

TEst.png

 

sunny_talwar

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

pipuindia99
Creator III
Creator III
Author

attached

sunny_talwar

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;