1 Reply Latest reply: Mar 17, 2015 5:14 AM by Renko Stuut RSS

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

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