5 Replies Latest reply: Jul 6, 2018 9:40 AM by Felip Drechsler RSS

    Load script in QlikSense with a function with 2 parameters

    Elton Aguiar

      Hi,

      I have a load script that works fine with a business days/holidays calculation as follows(before variable).

      But I need to use that calculation over and over and wanted to use a variable to have the code be cleaner.

       

      Before variable


      REQUESTDATA2:
      LOAD  *,//LAST DAY HOURS
      if(NetWorkDays([closed_at],[closed_at],'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01',
      
      
      ),Rangemin(rangemax(frac([closed_at]),maketime(9)),maketime(17))-Rangemax(rangemin(frac([closed_at]),maketime(9)),maketime(9)),0)*24 
      
      
      //CORRECT FIRST DAY=LAST DAY (HOURS)
      
      
      +if(NetWorkDays([closed_at],[closed_at],'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01',
      
      
      ) and floor([REQUESTDATA.opened_at])=floor([closed_at]),-MakeTime(8)*24,0)
      //FIRST DAY HOURS
      +if(NetWorkDays([REQUESTDATA.opened_at],[REQUESTDATA.opened_at],'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01',
      
      
      ),Rangemin(rangemax(frac([REQUESTDATA.opened_at]),maketime(17)),maketime(17))-Rangemax(rangemin(frac([REQUESTDATA.opened_at]),maketime(17)),maketime(9)),0) *24 
      
      
      //IN BETWEEN HOURS
      +(NetWorkDays([REQUESTDATA.opened_at]+1,[closed_at]-1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01',
      
      
      ))*8
            as CalcBusinessHours
      
      
      resident REQUESTDATA;
      
      DROP table  REQUESTDATA;
      RENAME TABLE REQUESTDATA2 to REQUESTDATA;
      

       

       

      Variable declaration (goes first)

       

      LET CALC=
      if(NetWorkDays($2,$2,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'),Rangemin(rangemax(frac($2),maketime(9)),maketime(17))-Rangemax(rangemin(frac($2),maketime(9)),maketime(9)),0)*24 
      
      
      //CORRECT FIRST DAY=LAST DAY (HOURS)
      
      
      +if(NetWorkDays($2,$2,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01') and floor($1)=floor($2),-MakeTime(8)*24,0)
      //FIRST DAY HOURS
      +if(NetWorkDays($1,$1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'),Rangemin(rangemax(frac($1),maketime(17)),maketime(17))-Rangemax(rangemin(frac($1),maketime(17)),maketime(9)),0) *24 
      
      
      //IN BETWEEN HOURS
      +(NetWorkDays($1+1,$2-1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'))*8;
      

       

       

      same code as before with attempt to use variable


      REQUESTDATA2:
      LOAD  *,
      $(CALC([REQUESTDATA.opened_at],[closed_at])) as CalcBusinessHours 
      //i.e. passing parameters $1 and $2 to the variable
      resident REQUESTDATA;
      
      DROP table  REQUESTDATA;
      RENAME TABLE REQUESTDATA2 to REQUESTDATA;
        
      

       

       

      Result of running code with the variable usage attempt...

      The following error occurred:

      Unexpected token: 'CalcBusinessHours', expected one of: ',', ':', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', ...

        • Re: Load script in QlikSense with a function with 2 parameters
          Felip Drechsler

          Hi Elton,


          By that message i'm assuming $(CALC([REQUESTDATA.opened_at],[closed_at])) is returning null giving the

           

          1. REQUESTDATA2: 
          2. LOAD  *, 
          3. as CalcBusinessHours  
          4. //i.e. passing parameters $1 and $2 to the variable 
          5. resident REQUESTDATA;

           

          Load statement and the error.

            • Re: Load script in QlikSense with a function with 2 parameters
              Elton Aguiar

              thanks you are right, for some reason $1 parameter doesn't seem to be recognize and letting me pass to a variable.

               

              does anyone know , Is that supported in QlikSense or is it a Qlikview only thing?

                • Re: Load script in QlikSense with a function with 2 parameters
                  Felip Drechsler

                  Also works on Sense,

                   

                  Just tried this on Sense Cloud (dont have it installed)

                   

                  x:
                  load * Inline
                  [
                  Teste
                      1
                  ];
                  
                  
                  set calc=$1+$2;
                  
                  
                  X2:
                  LOAD
                      Teste,
                      $(calc(5,6)) as CalcTest
                  Resident x;
                  
                  
                  drop table x;
                  

                  Works fine.

                   

                  sample.png

                    • Re: Load script in QlikSense with a function with 2 parameters
                      Elton Aguiar

                      Awesome!

                       

                      Revised code below, after I took out the // comments and put calc2, on a single line it worked great, appreciate your help Felip!

                       

                      set calc=$1+$2; 

                      SET calc2=if(NetWorkDays($2,$2,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'),Rangemin(rangemax(frac($2),maketime(9)),maketime(17))-Rangemax(rangemin(frac($2),maketime(9)),maketime(9)),0)*24   +if(NetWorkDays($2,$2,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01') and floor($1)=floor($2),-MakeTime(8)*24,0)  +if(NetWorkDays($1,$1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'  ),Rangemin(rangemax(frac($1),maketime(17)),maketime(17))-Rangemax(rangemin(frac($1),maketime(17)),maketime(9)),0) *24   +(NetWorkDays($1+1,$2-1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'  ))*8 ;


                      REQUESTDATA2: 

                      LOAD  *,//LAST DAY HOURS 

                      if(NetWorkDays([closed_at],[closed_at],'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'),Rangemin(rangemax(frac([closed_at]),maketime(9)),maketime(17))-Rangemax(rangemin(frac([closed_at]),maketime(9)),maketime(9)),0)*24  

                      //CORRECT FIRST DAY=LAST DAY (HOURS) 

                       

                       

                      +if(NetWorkDays([closed_at],[closed_at],'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01') and floor([REQUESTDATA.opened_at])=floor([closed_at]),-MakeTime(8)*24,0) 

                      //FIRST DAY HOURS 

                      +if(NetWorkDays([REQUESTDATA.opened_at],[REQUESTDATA.opened_at],'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'  ),Rangemin(rangemax(frac([REQUESTDATA.opened_at]),maketime(17)),maketime(17))-Rangemax(rangemin(frac([REQUESTDATA.opened_at]),maketime(17)),maketime(9)),0) *24  

                       

                       

                      //IN BETWEEN HOURS 

                      +(NetWorkDays([REQUESTDATA.opened_at]+1,[closed_at]-1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'  ))*8 

                            as CalcBusinessHours,

                              $(calc(5,6)) as CalcTest ,

                              $(calc2([REQUESTDATA.opened_at],[closed_at])) as CalcTest2

                             

                        resident REQUESTDATA; 

                       

                      DROP table  REQUESTDATA; 

                      RENAME TABLE REQUESTDATA2 to REQUESTDATA; 

                       

                      cleaned it up a bit, FYI I am using QlikSense Desktop for now with a plan to transition to server,

                      I prefer to not touch the auto-generated section, so I've just added the function itself under "functions" section and

                      loading of each table in categories in its own section after the functions section.

                      SET FUNC_BUSINESSDAYS=if(NetWorkDays($2,$2,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'),Rangemin(rangemax(frac($2),maketime(9)),maketime(17))-Rangemax(rangemin(frac($2),maketime(9)),maketime(9)),0)*24   +if(NetWorkDays($2,$2,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01') and floor($1)=floor($2),-MakeTime(8)*24,0)  +if(NetWorkDays($1,$1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'  ),Rangemin(rangemax(frac($1),maketime(17)),maketime(17))-Rangemax(rangemin(frac($1),maketime(17)),maketime(9)),0) *24   +(NetWorkDays($1+1,$2-1,'2016-01-01','2016-02-15','2016-03-25','2016-05-23','2016-07-01','2016-08-01','2016-09-05','2016-10-10','2016-12-26','2016-12-27','2017-01-02','2017-02-28','2017-04-14','2017-05-22','2017-07-03','2017-08-07','2017-09-04','2017-10-09','2017-12-25','2017-12-26','2018-01-01','2018-02-19','2018-03-30','2018-05-21','2018-07-02','2018-08-06','2018-09-03','2018-10-08','2018-12-25','2018-12-26','2019-01-01'  ))*8 ;
                      
                      
                      REQUESTDATA2:  
                      LOAD  *,//LAST DAY HOURS  
                              $(FUNC_BUSINESSDAYS([REQUESTDATA.opened_at],[closed_at])) as CalcBusinessHours       
                        resident REQUESTDATA;  
                      DROP table  REQUESTDATA;  
                      RENAME TABLE REQUESTDATA2 to REQUESTDATA;