Fieldvalue to variable cant get networking days to work.

Mon, 16 Mar 2015 20:24:33 GMT

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..,

Re: Fieldvalue to variable cant get networking days to work.

Tue, 17 Mar 2015 09:14:54 GMT

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.

topic Re: Fieldvalue to variable cant get networking days to work. in QlikView

Fieldvalue to variable cant get networking days to work.

Re: Fieldvalue to variable cant get networking days to work.