How to imitate two dimensional arrays in qlikview script

Below is a piece of qlikview script code which imitates two dimensional arrays:

2D array is supposed to be in the format like "MyArray" in the code below.
"GetItem" sub gets a item and assigns it to the given variable.
"FindItem" sub finds the item which corresponds to the given first dimension value and assigns it to the given variable.


Sub GetItem(fromArray,intoVar,X,Y)
Let firstDim=SubField(fromArray,',',X);
Let $(intoVar)=PurgeChar(SubField(firstDim,';',Y),Chr(39));
Set firstDim=NULL();
End Sub

Sub FindItem(fromArray,intoVar,SearchFor)
let k=fromArray;
For each t in $(fromArray)
Let s=t;
if SubField(t,';',1)=SearchFor then
Let $(intoVar)=SubField(t,';',2);
Exit for;
end if
next
End Sub


Set MyArray="'A;Turkey','C;Usa','D;Canada'";
Set vGetTest=Null();
Set vFindTest=Null();

Call GetItem(MyArray,'vGetTest',1,2); //This line sets 'vGetTest' to 'Turkey'
Call FindItem(MyArray,'vFindTest','C'); //This line sets 'vFindTest' to 'Usa'

Qlikview: Server: How to monitor a folder and trigger a QVS EDX task

I built a desktop windows application which monitors a certain folder and triggers an EDX task on qlikview server when a file is copied into the folder. The application also provides archiving option.

If you would like to try it yourself, please do the following steps:

1) Create an EDX task on the qlikview server:

2) Copy the EDX_Trigger_Desktop.exe and EDX_Trigger_Desktop.exe.config into the same folder, modify the values in the following entries of the config file according to your setup and run the exe file.
<add key="QMSAPIURL" value="http://localhost:4799/QMS/Service"/>
<add key="Taskname" value="EDXTEST"/>
<add key="TaskPassword" value="EDXTEST"/>
<add key="FolderToMonitor" value="C:\TEMP"/>
<add key="ArchiveFolder" value="C:\TEMP\ARCHIVE"/>

3) The application will display the values (folder to monitor, QMS URL, etc.) from configuration files. if necessary, these values can be modified manually in this screen:

Click the START button to start folder monitoring.

4) Copy a file into the folder which is being monitored and check if the EDX task is triggered on qlikview server.



The application and source files can be downloaded from the following links:

• Configuration and executable files:  release.zip
• Complete C# project:  EDX_Trigger_Desktop.zip

Facts, known issues, etc.:

• The application is developed with MS Visual Studio 2010 Express
• It triggers an EDX task on Qlikview Server version 11.

Qlikview: Security 3: Data Reduction 3

Scenario:

We have a sales team in which every salesman is assigned to a market (e.g. USA, France, etc...) and mainly make sales in their market but are also allowed to make sales out of market. For example a USA salesman mostly makes sales in USA but sometimes makes sales in European countries too.

In such a case, the USA manager should be able to see all the operations of his team, including their out of market sales in addition to USA sales.

Solution:


In this case, we can create a intermediate table which keeps salesmen's main market, and we can do the data reduction over this table's market field. By this way, we will be able to do the data reduction of main sales table by Salesman field rather than Market field.

When we filter/reduce the data in the intermediate table (the one on the left) by market, it will filter/reduce the data in the main sales table by salesman:

Here is the link for implemented solution:

https://sites.google.com/site/quickdevtips/home/security3.qvw?attredirects=0&d=1

Please log in with the following userid, password pairs and observe how data is filtered/reduced. When you login as ADM, you can see all data and also the script.

userid     password

---------  -----------

ADM        ADM

USmanager  USmanager

FRmanager  FRmanager

Qlikview: Security 2: Data Reduction 2

If we need to limit data according to one's position in employee hierarchy, let's say if everybody can only see the data of the people who are reporting to himself/herself, then we can use the hierarchical table created by HierarchyBelongsTo, to limit the data access:

1) If it does not exist, create the adjacent nodes table by HierarchyBelongsTo statement. This table will have a AncestorName (can be named differently) field which keeps the ancestors of each employee.

2) Include AncestorName field in Section Access table, and enter the person's name who will only be able to access the data of the people who are reporting to himself/herself:

ACCESS USERID PASSWORD COUNTRY STATE REGION CONTINENT ANCESTORNAME
------ ------ -------- ------- ----- ------ --------- ------------

USER   DAVIDP DAVIDP   ALL     ALL   ALL    ALL       DAVID PROBST

For demonstration purposes, I have also created expanded nodes table by Hierarchy statement, this table lets me to display the employee hierarchy in a treeview listbox. Please download the following application and login as  ADMIN (password is also ADMIN) or DAVIDP (password is also DAVIDP) and see how data is reduced. When you login as ADMIN you can see all the data and also the script.

https://sites.google.com/site/quickdevtips/home/security2.qvw?attredirects=0&d=1

Qlikview: Security 1: Data Reduction 1

Sometimes, we may need to filter our data according to logged in user's geographical profile. If we have a well structured geographical data associated with our fact data, then the solution is easy for the following scenarios. Please note that I assumed, we have fields named as COUNTRY, STATE, REGION and CONTINENT in our data.

1) If the user Jean-Paul is only allowed to see France data, then we can add the following row into our Section Access table and reduce the data to France only for Jean-Paul:

ACCESS  USERID  PASSWORD  COUNTRY  STATE  REGION  CONTINENT
------  ------  --------  -------  -----  ------  ---------
USER    JPAUL   JPAUL     FRANCE   ALL    ALL     ALL


2) If the user Brian is allowed to see United Kingdom and Ireland data, then we can add the following rows into our Section Access table and reduce the data to United Kingdom and Ireland only for Brian:

ACCESS  USERID  PASSWORD  COUNTRY  STATE  REGION  CONTINENT
------  ------  --------  -------  -----  ------  ---------
USER    BRIAN   BRIAN     UK       ALL    ALL     ALL

USER    BRIAN   BRIAN     IRELAND  ALL    ALL     ALL

3) If the user EuropeManager is allowed to see data of all European Countries, then we only need to add the following row into our Section Access table:

ACCESS  USERID   PASSWORD  COUNTRY  STATE  REGION  CONTINENT

------  -------  --------  -------  -----  ------  ---------

USER    EUR_MAN  EUR_MAN   ALL      ALL    ALL     EUROPE

4) If the user George is only allowed to see Florida (USA) data, we need to do the data reduction with two fields in Section Access table, as follows:

ACCESS  USERID   PASSWORD  COUNTRY        STATE    REGION  CONTINENT

------  -------  --------  -------------  -------  ------  ---------

USER    US_FL    US_FL     UNITED STATES  FLORIDA  ALL     ALL




5) If we need to have a regional filter for a user and the region is not defined in our data, then we can define the region in a separate table in Section Application. So, we have two steps to fullfill this requirement:

5.1) Define region by creating a new table (let's name it as REGIONS) in Section Application:

REGION         COUNTRY

--------------  -------------

WESTERN_EUROPE AUSTRIA

WESTERN_EUROPE BELGIUM

WESTERN_EUROPE FRANCE

WESTERN_EUROPE GERMANY

WESTERN_EUROPE LIECHTENSTEIN

WESTERN_EUROPE LUXEMBOURG

WESTERN_EUROPE MONACO

WESTERN_EUROPE NETHERLANDS

WESTERN_EUROPE SWITZERLAND

5.2) Add the following row to the into our Section Access table:

ACCESS  USERID  PASSWORD  COUNTRY  STATE  REGION          CONTINENT
------  ------  --------  -------  -----  --------------  ---------
USER    WEUR    WEUR      ALL      ALL    WESTERN_EUROPE  ALL

Following is the link for the implemented solution, please login with the userid and password pairs mentioned above and observe how data is reduced. When you login with userid ADMIN (password is also ADMIN), you can see the all data and also access the script.

https://sites.google.com/site/quickdevtips/home/security1.qvw?attredirects=0&d=1

Qlikview: Calculated Dimensions 2: Cumulative Sum

If we have a gap in our data, then Full Accumulation mode (in expressions tab of chart properties) may not work as we like.

For example, if we have sales data for a country over 12 months and if there is no sales data for August, we will have a chart which is missing August, as follows:

And we will also miss the data for the country (in this example it is Turkey) in a Full Accumulation mode, please see the August bar in the following chart:

Normally, since there is no data for August, we would expect to see the same value in August as July, in a cumulative sum chart.

To resolve this, we can do the followings:

1. Use calculated dimensions for 12 moths. Such a calculated dimension ensures that we have 12 months displayed in the chart
   vMonths='Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'
   calculated dimension =valuelist($(=vMonths))
2. Deactivate suppress zero values option in the presentation tab of chart properties
3. Activate Full Accumulation mode in the expressions tab

Please see the August bar in the resulting chart below, now it display data for Turkey in August bar:

Here is the qlikview file:

https://sites.google.com/site/quickdevtips/home/CalcDimCumulativeSum.qvw?attredirects=0&d=1

Qlikview: How to find a dimension value which corresponds to MAX or MIN of an expression

Scenario:
We need to find the countries who have the MAX and MIN sales and store them in variables to use in  set analysis.




Solution:
FIRSTSORTEDVALUE function can sort the fields according to a sort-weight (another field or an aggregation) and return the corresponding value of the field.


In our case we need to sort the countries by their total sales, thus we will use AGGR(sum(sales),country) as our sort-weight in FIRSTSORTEDVALUE function:

vCountryWithMaxSales =FirstSortedValue(Country, -Aggr(sum(Sales),Country))


The above expression returns the country which has the MAX sales. 


By default, the FIRSTSORTEDVALUE function sorts in ascending order. For sorting in descending order, we use the minus sign in front of the sort-weight. 


So, if we would like to get the country which has the MIN sales, we can just remove the minus sign:

vCountryWithMinSales =FirstSortedValue(Country, Aggr(sum(Sales),Country))

Warning:

If more than one country have the same MAX or MIN sales, then the FIRSTSORTEDVALUE function will return NULL. To avoid this, we can use DISTINCT qualifier:

vCountryWithMaxSales=FirstSortedValue(Distinct Country,-Aggr(sum(Sales),Country))
vCountryWithMinSales=FirstSortedValue(Distinct Country,-Aggr(sum(Sales),Country))



Sample:
https://sites.google.com/site/quickdevtips/home/findmax.qvw?attredirects=0&d=1