Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

eltonaguiar
New Contributor III

Load script in QlikSense with a function with 2 parameters

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

Tags (1)
1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: Load script in QlikSense with a function with 2 parameters

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

5 Replies
felipedl
Valued Contributor III

Re: Load script in QlikSense with a function with 2 parameters

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.

eltonaguiar
New Contributor III

Re: Load script in QlikSense with a function with 2 parameters

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
Valued Contributor III

Re: Load script in QlikSense with a function with 2 parameters

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

eltonaguiar
New Contributor III

Re: Load script in QlikSense with a function with 2 parameters

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
Valued Contributor III

Re: Load script in QlikSense with a function with 2 parameters

Nice,

Glad i could help .

Community Browser