Load script in QlikSense with a function with 2 parameters

[Anonymous]

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

[felipedl]

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.

[Anonymous]

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?

[felipedl · Accepted solution]

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.

[Anonymous]

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;  

[felipedl]

Nice,

Glad i could help .