Sunday 22 March 2015

Autonumber with AutoID

autonumber(expression[ , AutoID])
Returns a unique integer value for each distinct evaluated value of expression encountered during the script execution. This function can be used e.g. for creating a compact memory representation of a complex key.

In order to create multiple counter instances if the autonumber function is used on different keys within the script, an optional parameter AutoID can be used for naming each counter. 

Run the script:
TestAutoNumber:
Load * Inline [
Field1, Field2, Field3, Field4
1, 2, 3, 4
5, 6, 7, 8
9, 10, 11, 12
];

Table1:
LOAD
        *,
        AutoNumber(Field1, 'counter1') as a,
        AutoNumber(Field2, 'counter1') as b,
        AutoNumber(Field3, 'counter2') as c
Resident
        TestAutoNumber;

DROP Table TestAutoNumber;

Add table box:

Following  animation demostrates how counter1 and counter2 had been created

Copyright © 2015 Ruslan Goncharov

Mapping and ApplyMap

Today I want to discuss how to use Mapping Tables in QlikView. 
Mapping Tables can be used in scenarios where code is used to get description. Mapping Tables should have only two columns.
In Qlikview it is always advisable to minimize the number of tables in the data model.

Suppose we've got two tables:
SalesTable:
OrderId, ProductId
1, 12
1, 13
3, 16
4, 22

and

ProductTable:
OrderId, ProductId
12, 12th
12, another one description
13, 13th
14, 14th
15, 15th
16, 16th
17, 17th

Load following script:
SalesTable:
Load *
Inline [
OrderId, ProductId
1, 12
1, 13
3, 16
4, 22
];

Product_map:
mapping LOAD * INLINE [
ProductId, Description
12, 12th
12, another one description
13, 13th
14, 14th
15, 15th
16, 16th
17, 17th
];

SalesFact:
LOAD *,
     ApplyMap('Product_map', ProductId, 'No data') as ProductDescription
     Resident SalesTable;
     
// This table is longer needed in the FactTable
DROP Table SalesTable;


In fact Map in QlikView is a pair of <type, mapped_type> with 1:1 relationship.
To get information from map we have to use ApplyMap function.

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] ) 
where:
mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement. Its name 
must be enclosed by single, straight Quotation Marks in Scripting. 
expr is the expression, the result of which should be mapped. 
defaultexpr is an optional expression which will be used as a default mapping value if the mapping table does not contain a matching value for expr. If no default value is given, the value of expr will be returned as is. 

Take a look at Product_map closer.
1. There is no information about product with id=22 and we see No data message 



2. I intentionally duplicated information about product with id=12 in the map.

Despite of this ApplyMap returns only one (first) value.

Generally ApplyMap works like VLookup.

Note:
Keyword Resident means loading data from previously loaded table (SalesTable);
After loading data from SalesTable we may drop this table bacause SalesTable is longer needed in the FactTable;
Copyright © 2015 Ruslan Goncharov

Conditional Functions

Let's talk about Conditional Functions that can be used in QlikView.

Load following script and let's analyse what happen to our data:
set conIF =             if              ($1>=5, 'ok', 'no');
set conAlt =            Alt             (Num($1), Num($2), 'nothing');
set conPick1 =          Pick            ($1, 1,2,3,4,5,6,7,8,9,10);
set conPick2 =          Pick            ($1, 1,2,3,4,50,6,7);
set conMatch =          Match           ($1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');
set conMixMatch =       mixMatch        ($1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');
set conWildMatch =      wildMatch       ($1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f*');

set conClass10 =        Class           ($1, 4);
set conClass11 =        Class           ($1, 5, 'Y');
set conClass21 =        Class           ($1, 5, 'Y', 1);
set conClass22 =        Class           ($1, 5, 'Y', 2);
set conClass23 =        Class           ($1, 5, 'Y', 3);

TestTable:
Load
*,
$(conIF(Field1)) as sttIF, $(conAlt(Field1, Field2)) as sttAlt, $(conPick1(Field1)) as sttPick1, $(conPick2(Field1)) as sttPick2,
$(conMatch(Field1)) as sttMatch, $(conMixMatch(Field1)) as sttMixMatch, $(conWildMatch(Field1)) as sttWildMatch,
$(conClass10(Field1)) as sttClass10, $(conClass11(Field1)) as sttClass11,
$(conClass21(Field1)) as sttClass21, $(conClass22(Field1)) as sttClass22, $(conClass23(Field1)) as sttClass23

Inline [
Field1, Field2
0
1
2
3
4
5
6
7
8
9
10
a,1
b,2
c
A
B
C
foo
];



if(condition , then , else)
The three parameters condition, then and else are all expressions. The first one, condition, is interpreted logically. The two other ones, then and else, can be of any type. They should preferably be of the same type. If condition is true, the function returns the value of the expression then. If condition is false, the function returns the value of the expression else

if(Field1>=5, 'ok', 'no')
Under red square we see  lines where Field1>=5
I'd like to underline that Field1 has mixed data (numeric and string), so we may see lines under green square

alt (case1[ , case2 , case3 , ...] , else)
The Alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Alt(Num(Field1), Num(Field2), 'nothing');


The first condition Field1 has numeric data (red square), if no - the second condition Field2 has numeric data (orange square), if no - 'nothing' (yellow square). 
As you see Alt function is analog of IIF statement.
   
pick(n, expr1[ , expr2,...exprN])
Returns the n:th expression in the list. n is an integer between 1 and N.

set conPick1 = Pick (Field1, 1,2,3,4,5,6,7,8,9,10);
set conPick2 = Pick (Field1, 1,2,3,4,50,6,7);
It's not difficult to see that pick works like switch ... case statement.


match( str, expr1 [ , expr2,...exprN ] )
The match function performs a case sensitive comparison


mixmatch( str, expr1 [ , expr2,...exprN ] )
The mixmatch function performs a case insensitive comparison.

wildmatch( str, expr1 [ , expr2,...exprN ] )

The wildmatch function performs a case insensitive comparison and permits the use of wildcard characters ( * and ?) in the comparison strings. 

set conMatch = Match(Field1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');
set conMixMatch = mixMatch(Field1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f');
set conWildMatch = wildMatch(Field1, 1,2,3,4,5,6,7,8,9,10, 'A','B','C','a','b','cC','f*');


class(expression, interval [ , label [ , offset ]])
Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

set conClass10 = Class(Field1, 4);
set conClass11 = Class(Field1, 5, 'Y');
set conClass21 = Class(Field1, 5, 'Y', 1);
set conClass22 = Class(Field1, 5, 'Y', 2);
set conClass23 = Class(Field1, 5, 'Y', 3);



Class10 grouped by 4 lines;
Class11 grouped by 5 lines;

Class21 grouped by 5 lines and shifted by 1;
Class22 grouped by 5 lines and shifted by 2;
Class23 grouped by 5 lines and shifted by 3;

Copyright © 2015 Ruslan Goncharov

Tuesday 17 March 2015

Simpliest Extension Object Part 2

Let's vitalize our extension object.
Take a look at Accordion sample


Just create inside of RusGon new folder (Accordion) and copy content of SimpliestExtObj (Definition.xml, Icon.png, Script.js) into Accordion folder.
Change Definition.xml like this:
<?xml version="1.0" encoding="utf-8"?>
<ExtensionObject Label="Accordion" Description="Accordion" PageHeight="100000">
  <Dimension Label="Dimension1_RS" Initial="State" DropTarget="State"/>
  <Initiate Name="Caption.Text" Value="Accordion" />
</ExtensionObject>

Edit Script.js
Open  Accordion sample and take a look at html code of page (Ctrl+U in Google Chrome).
Copy following part
$(document).ready(function(){
       
        $(".accordion h3:first").addClass("active");
        $(".accordion p:not(:first)").hide();

        $(".accordion h3").click(function(){
                $(this).next("p").slideToggle("slow")
                .siblings("p:visible").slideUp("slow");
                $(this).toggleClass("active");
                $(this).siblings("h3").removeClass("active");
        });

});
into Script.js, but these lines 
<style type="text/css">
body {
        margin: 10px auto;
        width: 570px;
        font: 75%/120% Arial, Helvetica, sans-serif;
}
.accordion {
        width: 480px;
        border-bottom: solid 1px #c4c4c4;
}
.accordion h3 {
        background: #e9e7e7 url(images/arrow-square.gif) no-repeat right -51px;
        padding: 7px 15px;
        margin: 0;
        font: bold 120%/100% Arial, Helvetica, sans-serif;
        border: solid 1px #c4c4c4;
        border-bottom: none;
        cursor: pointer;
}
.accordion h3:hover {
        background-color: #e3e2e2;
}
.accordion h3.active {
        background-position: right 5px;
}
.accordion p {
        background: #f7f7f7;
        margin: 0;
        padding: 10px 15px 20px;
        border-left: solid 1px #c4c4c4;
        border-right: solid 1px #c4c4c4;
}
</style>
</head>

<body>

<div class="accordion">
        <h3>Question One Sample Text</h3>
        <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>
        <h3>This is Question Two</h3>
        <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>
        <h3>Another Questio here</h3>
        <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>
        <h3>Sample heading</h3>
        <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>
        <h3>Sample Question Heading</h3>
        <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>
</div>
we need prepare a bit. Technically we need to
replace all double quotes by single quotes;

each line should be enbordered by double quotes;

Following VBA code demonstrates this technique:
Private Sub btnTransform_Click()
    Dim txt As String
    Dim out As String
    Dim strArray() As String
    Dim i As Integer
   
    out = ""
   
    If Not IsNull(txtFrom.Value) Then
        txt = txtFrom.Value
        If txt <> "" Then
            txt = Replace(txt, """", "'")
           
            strArray() = Split(txt, vbCrLf)
           
            For i = LBound(strArray) To UBound(strArray)
                out = out & """" & strArray(i) & """+" & vbCrLf
            Next
                   
            txtTo.Value = out
           
            Me.txtTo.SetFocus
            DoCmd.RunCommand acCmdCopy
        End If
    End If
   
End Sub
 
At the end Script.js should look like this:
Qva.LoadScript('http://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js', function() {  
          Qva.AddExtension('QlikView/Examples/RusGon/Accordion', function(){  
                   //Begin extension code.  
                        this.Element.innerHTML =
                        "<HTML><BODY> "+

                        "<style type='text/css'>"+
                        "body {"+
                        "       margin: 10px auto;"+
                        "       width: 570px;"+
                        "       font: 75%/120% Arial, Helvetica, sans-serif;"+
                        "}"+
                        ".accordion {"+
                        "       width: 480px;"+
                        "       border-bottom: solid 1px #c4c4c4;"+
                        "}"+
                        ".accordion h3 {"+
                        "       background: #e9e7e7 url(images/arrow-square.gif) no-repeat right -51px;"+
                        "       padding: 7px 15px;"+
                        "       margin: 0;"+
                        "       font: bold 120%/100% Arial, Helvetica, sans-serif;"+
                        "       border: solid 1px #c4c4c4;"+
                        "       border-bottom: none;"+
                        "       cursor: pointer;"+
                        "}"+
                        ".accordion h3:hover {"+
                        "       background-color: #e3e2e2;"+
                        "}"+
                        ".accordion h3.active {"+
                        "       background-position: right 5px;"+
                        "}"+
                        ".accordion p {"+
                        "       background: #f7f7f7;"+
                        "       margin: 0;"+
                        "       padding:  10px 15px 20px;"+
                        "       border-left: solid 1px #c4c4c4;"+
                        "       border-right: solid 1px #c4c4c4;"+
                        "}"+
                        "</style>"+
                        "</head>"+
                        ""+
                        "<body>"+
                        ""+
                        "<div class='accordion'>"+
                        "       <h3>Question One Sample Text</h3>"+
                        "       <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>"+
                        "       <h3>This is Question Two</h3>"+
                        "       <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>"+
                        "       <h3>Another Questio here</h3>"+
                        "       <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>"+
                        "       <h3>Sample heading</h3>"+
                        "       <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>"+
                        "       <h3>Sample Question Heading</h3>"+
                        "       <p>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Morbi malesuada, ante at feugiat tincidunt, enim massa gravida metus, commodo lacinia massa diam vel eros. Proin eget urna. Nunc fringilla neque vitae odio. Vivamus vitae ligula.</p>"+
                        "</div>"+
               
                        "</BODY></HTML>";      

                        $(document).ready(function(){
         nbsp;                       $(".accordion h3:first").addClass("active");
                                $(".accordion p:not(:first)").hide();

                                $(".accordion h3").click(function(){
                                        $(this).next("p").slideToggle("slow")
                                        .siblings("p:visible").slideUp("slow");
                                        $(this).toggleClass("active");
                                        $(this).siblings("h3").removeClass("active");
                                });                                                            
                        });                                                    
          });  
 });

Open QlikView and we see Accordion Object

Simpliest Extension Object

A QlikView Extension allows you to visualize your data the way you want to see it.

Let's create brand new Extension Object.
First of all we need to find the folder where QlikView's extensions are placed in.
In my computer path is C:\Users\<user_name>\AppData\Local\QlikTech\QlikView\Extensions\Objects\QlikView\Examples\

 I suggest create a new subfolder inside of Examples folder to store our extension objects (e.g. RusGon).
inside of RusGon we create a new folder SimpliestExtObj. It will be new Extension Object. Inside of this folder shoul be following files:
Definition.xml
Icon.png
Script.js
Definition.xml
<?xml version="1.0" encoding="utf-8"?>
<ExtensionObject Label="SimpliestExtObj" Description="description of SimpliestExtObj" PageHeight="100000">
  <Dimension Label="Dimension1_RS" Initial="State" DropTarget="State"/>
  <Initiate Name="Caption.Text" Value="Caption of SimpliestExtObj" />
</ExtensionObject>
Script.js
 Qva.LoadScript('http://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js', function() {  
          Qva.AddExtension('QlikView/Examples/RusGon/SimpliestExtObj', function(){  
                   //Begin extension code.  
                        this.Element.innerHTML =
                        "<HTML><BODY> "+
               
                        "</BODY></HTML>";      

                        $(document).ready(function(){
                                                       
                        });                                                    
          });  
 });
Skeleton of SimpliestExtObj you may download at GoogleDrive

Now the time to see our SimpliestExtObj in work. 
Open QlikView and Turn on WebView

 Choose New Sheet Obect

Under Extension Obects node we see our SimpliestExtObj. Drag it and drop at sheet

Congratulations! New extension object has been created!

Monday 16 March 2015

Reload vs Partial Reload

Suppose we've got a table like this:
Office:
LOAD * INLINE [
    EmployeeID, Name
    1, 100
    2, 200
    3, 300
    4, 400
    5, 500
    6, 600
];
Field EmployeeID looks like
Change a bit the script
Office:
LOAD * INLINE [
    EmployeeID, Name
    1, 100
    2, 200
    3, 300
    4, 400
    5, 500
    6, 600
];  

Office:
LOAD * INLINE [
 EmployeeID, Name
 6, 600
 7, 700
];
run again it (Ctrl+R) and take a look at EmployeeID field
we may see new seventh employee
But if we change a bit properties of EmployeeID field (Show Frequency)

we will see that the sixth employee presented twice
To avoid duplicating we need to check EmployeeID field like this:
Office:
LOAD * INLINE [
    EmployeeID, Name
    1, 100
    2, 200
    3, 300
    4, 400
    5, 500
    6, 600
];  

Office:
LOAD * INLINE [
 EmployeeID, Name
 6, 600
 7, 700
]
Where not Exists(EmployeeID);

It's a key moment.

Now change a script like this (ADD ONLY)
Office:
LOAD * INLINE [
    EmployeeID, Name
    1, 100
    2, 200
    3, 300
    4, 400
    5, 500
    6, 600
];  

Office:
ADD ONLY LOAD * INLINE [
 EmployeeID, Name
 6, 600
 7, 700
]

Where not Exists(EmployeeID);
and Run it(Ctrl+R). We see 
but if we Run Partial (Ctrl+Shift+R) we see 
At the and let's come back to previous script without ADD ONLY and Exists keywords
Office:
LOAD * INLINE [
    EmployeeID, Name
    1, 100
    2, 200
    3, 300
    4, 400
    5, 500
    6, 600
];  

Office:
ADD LOAD * INLINE [
 EmployeeID, Name
 6, 600
 7, 700
];
//Where not Exists(EmployeeID);

Every time we  Run Partial (Ctrl+Shift+R) we see that duplicates grow again

and again


Copyright © 2015 Ruslan Goncharov