Networkdays Issue

[pipuindia99]

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;

[sunny_talwar]

Try changing

LET vListOfHolidays = '=only(ListOfHolidays_date)';

to

LET vListOfHolidays = Peek('ListOfHolidays_date');

[sunilpaul]

Instead of LET in your expression, try to use SET

Regards,

[pipuindia99]

Tried, not working

[pipuindia99]

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]

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;

[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]

attached

[sunny_talwar · Accepted solution]

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;