Qlik Tidbit – 4

Current Quarter Formula: 

Let vCurrentQtr = ceil(month(Today())/3);

‘Q’ & $(vCurrentQtr);

Previous Quarter Formula: 

Let vPreviousQtr = if(Mod(fabs(vCurrentQtr-1),4)=0, 4, Mod(fabs(vCurrentQtr-1),4));

Previous Quarter Year Formula:

Let vCurrentYear = Year(Today());

Let vPreviousYear = vCurrentYear;

Let vPrevQtrYear = if (vPreviousQtr=4, vPreviousYear, vCurrentYear);

QlikView Supporting Tasks for executing DOS commands

Using QlikView Supporting task to execute dos commands.

QMC->Supporting Tasks -> External Program – > Create new task

Use below syntax with actuals in ‘Command line statement’

“C:\Windows\system32\cmd.exe” /C move /Y “\\source_path\*.txt” “\\destination_path\Archive\”

QlikView Error Message – “Error: Error in expression: PEEK is not a valid function”

Was thinking of having a separate category for QlikView error message for quite some time, fortunately it’s happening with this post.

“Error: Error in expression: PEEK is not a valid function”

Cause: 

Used Set statement for storing the result of peek expression in Script.

Set vVar = peek();

Solution:

Replace Set with Let.

Otherwise go as below,

Let vTemp=peek(..);

Set vVar=$(vTemp);

And also consider what if peek expression returns null value. That variable itself will not be available.

But still $(vVar) will return null and $(#=vVar) will return zero.

QlikView Variables

Let vVarname1=1+1;
Set vVarname2=2+2;

$(vVarname1) will return 2.
$(vVarname2) will return 4.

Assuming variable is created in script and assigned value from expression.

If expression happens to be null what will happen to variable? Variable can be accessed as isNull(Var_name) in script and but it will not be available in GUI.

That is one situation where we might want to understand the difference between Let and Set.

Let vVarname3=peek(…); // Peek() returns null

Set vVarname4=peek(…); // Peek() returns null

If peek returns ‘null’ vVarname3 will not available in GUI but vVarname4 will be. vVarname4 can be accessed with $(), $(#), $(#-).

if($(vVarname3)=’One’) then //Left side of evaluating expression cannot be empty
……
End if

Result:
——
Error: Variable missing
if(‘$(vVarname3)’=’One’) then //Instead I put ”
……
End if

Result:
——
” = ‘one’ not equals so else part will be executed.

If it is numeric we can also try as below.
$(#=vVarname3) which will return 0
0=’one’ wrong comparison, have to be cautious about that.

Qlik Tidbit – 3

Small DOS commands can be executed directly using “External Program” task in QlikView.

Below command is for deleting a file from a sharepath.

“C:\Windows\system32\cmd.exe” /C del “\\server path\QlikView Source Document\Test\test.success”