Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Fieldvalue to variable cant get networking days to work.

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

Tags (1)
1 Reply
Not applicable

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

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.

Community Browser