QlikView System Variables

Copied below topic from QV help. Include, Must_Include and HidePrefix system variables are extensively used. If we could remember other available variables and using it in right Use Case, might help in coding better.

System Variables

These variables have special meanings to QlikView:

Floppy

Returns the drive letter of the first floppy drive found, normally a:. A system-defined variable.

CD

Returns the drive letter of the first CD-ROM drive found. If no CD-ROM is found, then c: is returned. A system-defined variable.

$(Include =filename )

$(Must_Include =filename )

The include and must_include variables specify a file that contains text that should be included in the script. The entire script can thus be put in a file. This is an user-defined variable.

The difference between include and must_include is that include will fail silently if the file is not found during script reload, while must_include will throw an error if the file is not found.

Examples:

$(Include=abc.txt);

$(Must_Include=def.txt);

I.e. the construction set Include =filename is not applicable.

HidePrefix

All field names beginning with this text string will be hidden in the same manner as the system fields. A user-defined variable.

Example:

set HidePrefix=’_’ ;

If this statement is used, the field names beginning with an underscore will not be shown in the field name lists when the system fields are hidden.

HideSuffix

All field names ending with this text string will be hidden in the same manner as the system fields. A user-defined variable.

Example:

set HideSuffix=’%’;

If this statement is used, the field names ending with a percentage sign will not be shown in the field name lists when the system fields are hidden.

QvPath

Returns the browse string to the QlikView executable. A system-defined variable.

QvRoot

Returns the root directory of the QlikView executable. A system-defined variable.

QvWorkPath

Returns the browse string to the current QlikView document. A system-defined variable.

QvWorkRoot

Returns the root directory of the current QlikView document. A system-defined variable.

StripComments

If this variable is set to 0, stripping of /*..*/ and // comments in the script will be inhibited. Certain database drivers using /*..*/ comments for defining hints in select statements may otherwise cause script errors. It is recommended that this variable be reset to 1 immediately after the statement(s) where it is needed. If this variable is not defined, stripping of comments will always be performed.

Example:

set StripComments=0;

Verbatim

Normally all field values are automatically stripped of preceding and succeeding blank characters (ASCII 32) before being loaded into the QlikView database. Setting this variable to 1 suspends the stripping of blank characters.

Example:

set Verbatim = 1;

OpenUrlTimeout

This variable defines the timeout in seconds that QlikView should respect when getting data from URL sources (e.g. HTML pages). If omitted the timeout is about 20 minutes.

Example:

set OpenUrlTimeout=10

WinPath

Returns the browse string to Windows. A system-defined variable.

WinRoot

Returns the root directory of Windows. A system-defined variable.

QlikView sourcing from Oracle: “Error: Field not found”

QlikView is case-sensitive on Field/Column names.

In Oracle, object names are stored in uppercase by default and it is not case-sensitive too. It can be made case-sensitive by explicitly using quotes, though.

Error: “Field not found – Field_Name”

becomes frequent error as the result of conflict between two technologies.

One such situation:

Executed below query and prompted with “Field not found” error message.

Load
sqlstr as Field_Name;
SQL Select
XXXXXXXXXXXXXX as sqlstr
From dual;

Cause:

sqlstr is stored in Oracle as SQLSTR. Since QlikView is case-sensitive on Field name, it throws this error.

To get a better picture of this, change preceding load as ” Load *; ” to see the way Oracle returns the field name.

Load
*;
SQL Select
XXXXXXXXXXXXXX as sqlstr
From dual;

Below image from QlikView data model (Ctrl + T) while using ” Loading *; “. Now it is obvious that Oracle returns the field name in uppercase.

Solution:

Change the field name to match Oracle way (Uppercase).

Useful Tools 2

Below websites help in choosing the color palette.

http://tools.medialab.sciences-po.fr/iwanthue/

http://colorbrewer2.org/

Courtesy: https://community.qlik.com/docs/DOC-6972

QlikView Previous and Next Selections

Recently I got a use case to use this feature Previous and Next selections of QlikView effectively.

Use case: 

“What if? Analysis” at both summary level and detailed level of charts. Due to some reasons unable to use QV group feature so created customized drill down by showing and hiding charts. The detailed chart will be shown upon selecting particular Reporting Date on the summary chart and this chart pops up with a set of sliders so the user can do “What if?”. The catch here when the user clears the Reporting date selection and goes back to Summary chart still he should be able to see the forecast for that particular chart unless he clears them completely.

Ok, now some basics about Previous/Forward selections.

{$N} – Previous Selection

{$_N} – Forward Selection

N is an integer.

Note: Till integer 5 there won’t be any issue, but after that you can see error highlighting. Initially, I thought it’ll work only for 5 previous and next selections that are wrong it can support more, I tested up to 50 previous selections.

Solution:

I thought below expression would do the trick, but it didn’t.

only({$1} Reporting_Date)

I was not getting consistent result, meaning sometime it returned null value as well. Problem could be due to the triggers in my QVW. So I came up with more robust logical expression. Basically it checks whether first previous selection {$1} has any selection if not it goes to next level and check, does the same for five levels {$5} this was solidly sufficient to meet my requirement.

=if(isnull(only({$1} [Reporting_Date])), if(isnull(only({$2} [Reporting_Date])), if(isnull(only({$3} [Reporting_Date])),if(isnull(only({$4} [Reporting_Date])), only({$5} [Reporting_Date]), only({$4} [Reporting_Date])),only({$3} [Reporting_Date])) ,only({$2} [Reporting_Date)), only({$1} [Reporting_Date]))

.. stored above expression in a variable and used it in appropriate places.

Spike in the image is forecasted value.

Note: Give option for user to clear the previous selections otherwise he might feel like locked up with something. I did it by creating a button and activating the trigger “Clear All” for five times.

 

 

 

 

 

 

Qlik Tidbit – 7

Below expression (syntax) can be used to show only the selected values of list box.

=aggr(only(Field_name), Field_name)

Qlik Tidbit – 6

If Data source has Date column with more than 3 characters for Month (possibly complete month name), it could be interpreted and used in QlikView/QlikSense as below.

date(date#(’08/September/2015′, ‘DD/MMMM/YYYY’), ‘MM/DD/YYYY’)

Note:

Date# – Interpreter function helps QV to understand the source format.

Date – Function used in QlikView to instruct the format that needs to be used for displaying, if format is not specified explicitly, default format will be taken.

Copying selections between QlikView Alternate States

Got a requirement which needs to copy/propagate the selections from one alternate state to another. Default state is not part of it.

Group A selections should reflect on Group B.

Solution:

Document Properties -> Triggers -> Field Event Triggers -> Dim2 (Field used in my example) -> OnSelect -> Action -> Select in Field

Below are the inputs for achieving this.

Field: Dim2

Search String: =only({[Group A}] Dim2)

Alternate State: Group B

Final result:

Happy Qlikking!!

Qlik Tidbit – 5

QlikView Shortcut key to bring the list of Shortcuts available in QlikView.

Ctrl + Q+S+C

Note: Hold Ctrl and press Q, S and C sequentially.

 

List box – Restrict the values

This post is about restricting values of List box based on different conditions.

Sample application that I created for this exercise has below data model.

List box without any restriction.

Restrict List box values based on condition for single metric field. Just display the products that has transaction amount.

=if(aggr(sum(Amount), [Product Name])>0, [Product Name])

Restrict Listbox values based on conditions for more than one metric fields. Product

=if(aggr(sum(Amount), [Product Name])>0 and aggr(sum(Quantity), [Product Name])>5, [Product Name])

Dynamic conditions based on field values.

=if(aggr(sum(Amount), [Product Name])>0 and [Product Category]<>’Dairy’, [Product Name], if(aggr(sum(Quantity), [Product Name])>10, [Product Name]) )

 

Have good one folks!!

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.