Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi a'm trying to load from a excel file that contains 2 fieds one date and one country field .
and use these to calc the networking days the first part is working normal..
[Inline2]:
LOAD concat(num(HolidayDate),',') as [Holiday],
CountryHolidayCode as COUNTRY
FROM
[Holidays.xlsx]
(ooxml, embedded labels)
Group by CountryHolidayCode;
SET holliday=networkdays($1,$2,$3);
SET holliday2=networkdays($1,$2,$3);
For j = 1 to NoOfRows('Inline2')
let D='networkdays_'&peek('COUNTRY',($(j)-1),'Inline2');
let D2='Holiday_'&peek('COUNTRY',($(j)-1),'Inline2');
let D3=peek('COUNTRY',($(j)-1),'Inline2');
let $(D) ='networkdays($1,$2,' & peek('Holiday',($(j)-1),'Inline2') & ')';
let $(D3)=peek('Holiday',($(j)-1),'Inline2');//this should create a for example $(NL) or $(DE) i can use this by hand not a problem..
next j;
But after this i try to calc the network days..
i trying to use the country field and then read the variable with the same name as inside the country field.
I hope someone can point me in the right direction..
I have seen some examples with BIG if statements but i hope this isn't needed becaurse of excel file with country information is changing fast .
Left join ([Raw data])
LOAD concat(num(HolidayDate),',') as [Holiday],
CountryHolidayCode as COUNTRY
FROM
[Holidays good file.xlsx]
(ooxml, embedded labels)
Group by CountryHolidayCode;
Calc:
load ITEM_KEY,
START_DATE,
CLOSE_DATE,
COUNTRY,
[Holiday],// Holliday field already contains num values of all date comma separated.
$(holliday([START_DATE],[CLOSE_DATE],[Holiday])) as test,//this doenst work reading direct field
$(holliday2([START_DATE],[CLOSE_DATE],$(=$([COUNTRY])))) as test2 cant get this to work to,
$(holliday2([START_DATE],[CLOSE_DATE],$(DE)) as test3 Works but static not variable..,
Maybe this is better.
I have a table with country and date field..
Country,Date
DE,1-1-2014
DE,6,1,2014
From this i create variables with the names of the country..
variable name DE with value 41640,41645
Then when i want to use it like static it works..
$(holliday2([START_DATE],[CLOSE_DATE],$(DE))
But i would like te be use it in a load script and use a Country field that contains DE and in some way
use this to link the $(DE) variable i tryed $(=$([COUNTRY])) but no luck ..
I hope this is better to understand.