Dual () Function in QlikView

QVNewbie#5 : Dual () Function in QlikView :

Dual () Function in QlikView is a most Powerful function which i personally use for sorting the data in most of my implementations. “Dual() function does the Artificial association of the String Field  to the given Number”.

Let`s take a sample Data to run through the Dual() function. I have a Days Field in my Data model. Now the Request is to sort this data as per the week Days. Data looks as below before sorting the data.

      

    Raw Data                                         QV Front end Before Sorting

                                 

           There are many ways to achieve the sorting Scenario, Below are few ways what i can imagine now.

1. IF statement with DUAL() Function (We will see this in Detail in this post)
2. Applymap with Dual Function (We`ll jump into this in my Next blog post “How to Use ApplyMap”) and
3. Using Inline Table with  two fields, One for Days and another field for Sorting Number. Linking the Days field from INLINE Table to Transaction tables Days field by Forming a External  association.Then use this Sorting Number field in Sort Property of the List Box to Sort the Days Field (Expression Sort).

Code looks like below for our 1st approach (IF Statement and Dual() Function ) :

In your data model, if the Sort Field is also available in Table, then You may use them as below.

Once the above code changes are completed, Run the Script to get this changes reflected at frontend. If we take the Days Field at Frontend, It should now automatically Sorted, if not Change the Default Sort of the Field to ‘Numeric’.

Hope this helps. Thanks for reading the post and Happy Qliking !!!

Alternate States in QlikView

QVNewbie#4 : Alternate States in QlikView :

Few days ago i was asked to create a basket analysis to compare set of products Vs other Set of products. Yes ,1st thing that strike my mind was Alternate States. . Alternate states are one of the powerful feature to disconnect your set of objects/ sheets from Datamodel. Let's discuss about creating alternate states and various use in this post.

How to Create Alternate State : 

Go to Settings >> Document Properties >> General >> Alternate States… >> Add >> New State Name

Note : Alternate States are called as State Identifiers.

Yes, Now the Alternate State is ready to use, Developer have access to create unlimited Alternate States (State Identifiers).

There are two type of state in Alternate State :

                       1) Default    : Default State is Nothing but the Current Selection $

                       2) Inherited : Inherited is the state which inherits the properties from the parent Document/Sheet/Object. i.e if it is Sheet, It get the state from Document, if it is object, it inherits the properties from Sheet / container. Sheet / object always set to inherit in new document.

Scenario 1 - Assign Alternate to Sheet : If alternate State is assigned to Sheet, the objects available within the sheet will inherit Sheet`s state.

To Assign Alternate State to any Sheet or object , Right Click on the object or sheet on which you`d like to set Alternate State >> Go to Properties >> General >> Alternate State >> Select the State

For Example if State identifier State1 is assigned to sheet, then the objects within the sheet will respond to State1 Alternate State. Object State within the sheet can be also overridden by changing it to the different State Identifier.

Scenario 2 - Alternate State in Expressions :  Let's see how to use the alternate state in expressions.

To illustrate the Alternate State in Expression,Will create a sample data with three fields (Invoice_Date, Order_Type and Region). Now Let's set the Sheet`s Alternate State to inherited which is Default state of the Sheet and Will Assign State1 to Invoice_date List box.

Expression 1 :  Count (ORDER_TYPE)

- Count of Order type is a Simple Exporession which is Current Selstion State. So Selection in Invoice_Date field will not affect the count of this expression as Invoice_date is set to State 1.

Expression 2 :  Count ({State1} ORDER_TYPE)

- Count of Order type, This expression will respond to the selections in Invoice Date field where the State1 State Identifier is Assigned.

Expression 3: Above Expression will respond to the object selections which are within State1 State identifier. However, can we make the above expression respond to the current Selection? Yes It is possible. For example, if i have Field called region in my data model which is in default State ($), Now we wanted this region field selection also to be included in above Expression.

This can be achieved by including the current selection as shown below.

Count ({State1<Region = $::Region>} ORDER_TYPE)

$ sign in the above expression, denotes the Current Selection State, the Simple expansion of the above Expression is Region field from current Selection state (one which is highlighted in pink) is Equated to the Region field in State1  (text which is highlighted in red). Current Selection ($) state can be replace with any other states available in Document.

This Expression can be reversed as below to set the Expression to work in Current Selection ($) state and one or more field from other state can be used within the Expression

Count ({$<Region = State1::Region>} ORDER_TYPE)

Expansion of the above Expression is, overall Expression is set to Current Selection State (one which is highlighted in pink) and Region field from State1 will be equated to Region field in ($) (One which has been Highlighted in Red)

Scenario 3 - Alternate State in Bookmark : Bookmarks capture selections in all states defined in the QlikView document. When recalling a bookmark created in version 11 (or later) the selections in all states will be applied.

For Example if the bookmark is created with the name ‘BookMark1’ with few selections in State1 and Current Selections, This can be used as below

Count ({$<State1::BookMark1>} ORDER_TYPE)

Few other Expressions using Alternate State :

To get the Selected count of field of a particular state,

GetSelectedCount(Invoice_date, ';', ,'State1')

To concatenate field of particular state,

Concat({State1} Chr(39)&Invoice_Date&Chr(39),',')

To find the Alternate State of the Sheet / object  : Statename()

Clear Specific State Selections : Selections on the specific Alternate Selection can be cleared by selecting the Arrow next to Clear Button from the top menu >> Clear Specific State >> State Identifier

Word of Caution : Alternate States might confuse users if this has not been used properly or Documented properly in dashboard.

Hope this helps. Thanks for reading the post and Happy Qliking !!!

How To Extract Data from Database Using QlikView

QVNewbie#3 :How To Extract Data from Database Using QlikView

We will be seeing  How to Connect QlikView to Oracle database. ODBC / OLED connection can be used to establish a Connectivity between QlikView and Oracle DB.

To Setup ODBC Connectivity, We need to have ODBC driver installed in your Computer. Once the ODBC Drivers are installed follow the below steps to Setup ODBC Connection.

• From Windows, click Start, then select Settings, Control Panel, Administrative Tools and Data Sources (ODBC). The ODBC Data Source Administrator window appears.

                       

• You can Setup the DSN based on User DNS or System DNS, Below is the Difference

For Example if Mike has logged in to the system and created a User DNS and Logged off. If Jerry Logs into the same System, Jerry will not see the DNS created by Mike since the DNS which Mike Created is User DNS and It is visible only to the user who Created it. But if this has been Created as System DNS, then that can be share by both of them. It is based on your Requirement whether you want to go with System or User DNS. Refer to Microsoft documentation for difference between User / System DNS.

• Click the System DSN tab, and click Add to create a new data source. The Create New Data Source window appears. Your Oracle client driver should appear on this list:
• Look at the difference between Setting up the

• Select your Oracle client driver from the list, and click Finish. The ODBC Oracle Driver Configuration window appears.
• In the Data Source Name field, type the name that you want to use for this ODBC data source.

• In the TNS Service Name field, type the name of your Oracle service. This is the name that is specified in the tnsnames.ora file that is defined for your Oracle client.

• In the User ID field, type the username that you use to log into the Oracle database. The Oracle ODBC Driver Configuration window should now look something like this:

Click OK to create the ODBC data source for your Oracle client. The new data source now appears in the ODBC Data Source Administrator window:

• Now your ODBC connection is ready to use. Open QlikView and go to script editor or Ctrl + E.
• Select ODBC from drop down menu of the Database as Show below.  You may also ‘Force it to 32 Bit’ (if you are using x64 bit windows machine  and you have  only the 32bit Oracle ODBC Driver installed in your system then you`ll have to force the ODBC to use ‘Force 32 Bit’).

                             

• Click Connect to see the list of Data sources which has been already setup from above steps. Select the Data Sources which you want to establish a Connection to QlikView and Key-in the Credentials and click OK

• Once Connection is successful, you`ll see the Connection string as shown below. Your Credentials will be Encrypted,

           

• Click Select to see the list of tables / Views / Synonyms available in the database. Select your required database object and say OK. You`ll see the ‘SQL Select’ Code in QlikView Script Editor and hit run in QlikView to Extract the data from Oracle Database.Above approach can be used in any database which allows ODBC connectivity.

            Thanks for reading the post and Happy Qliking !!!.

How to Create QlikView Container Objects

QVNewbie#2 : How to Create QlikView Container Objects

QlikView Container Objects is a useful object which holds set of other sheet objects and activate & De-Activate them as per the selection.

Before Container object was introduced in QlikView, Developers used to Minimize and Maximize the charts to have one of the Container object Functionality or Using the Variable to Hide and UnHide the Charts/Sheet objects.

Step 1: To Include Container object, Right Click on the Sheet >> New Sheet Object >> Container...

                              

Step 2:  Step 1 will take you to the 'New Container' window where it`ll list down the objects which is available in document. My Sheet has List box and Charts, So below container object Screen shot shows them for me to choose.

                              

Step 3: Chose your Objects which you`d like to place in the container object and click 'OK' button to view the object. To Demonstrate, i`ll be choosing the 'CH01 Stacked Bar With Line' and 'CH02 Linechart' objects in to my container object.

                            

Step 4: Once you click on 'OK' Button, You`ll See the container object with selected Sheet objects as shown below. 

                            

Set 5: The Default Appearance of the Container Objects is 'Tabs on the Left', Default appearance settings can be changed from the 'Presentation tab' of the Container Object.

                          

Step 6: Default Container Type can be changed from the 'Presentation tab' to 'Single Object' or 'Grid' View.

                         

Introducing container object helped to organize the QlikView sheet Objects effectively,It has saved the Developers time and beauty of the Container object is that it can hold any QlikView Sheet Objects which has opened the gate for us to do logical grouping of the sheet objects. This container objects can be used in various scenarios which i`ll be discussing in my future posts.

 Thanks for reading the post and Happy Qliking !!!

How to Create QlikView Chart with Both Stacked bars and Line

QVNewbie#1 : How to Create QlikView Chart with Both Stacked bars and Line

I have been working on QlikView for many years by now, but my passion for QlikView has never gone down and it is getting increased as the year moves on, I`m still learning new things and Exploring the QlikView World as a newbie. Created this blog to share the information which I know and whatever I have learnt from various sources and from my experience to QlikView lovers.

Though I’m an active participant / reader of the post in Qlik Community but this is my first blog post, I have tried Explaining as much as I could  by giving examples, Forgive me if my explanation is not Clear J. For Sure will try working hard to improve skills .

Ok, that`s my intro and let`s get in to the QlikView Newbie Story #1.

I had requirement recently from customers that to build chart which has Stacked bar with Line.I`m going to take sample data to illustrate the Scenario requested.

Here is a requirement which Customer want to see in QV.

Dimensions:

•   1st Dimension as Year,
•   2nd Dimension as Length of the Movie

Expressions:

• 1st Expression as Count of Movie (This Expression Should be Stacked based on 2^nd Dimension ‘Length of the Movie’)
• 2nd Expression as Average Rating of the movie.

By Year, Count of Movie has to be stacked based on the Second dimension and Average of Rating by Line.It is pretty impossible to get the scenario working by using the combo chart with two dimensions, one expression as Bar and one Expression as line in QlikView (As far as I know :D). So we need to create chart with multiple Expression for second dimension.

Let’s See how do we achieve this in QlikView.

Step 1: Right Click on the Sheet >> go to New Sheet Object >> Chose Chart…

    

Step 2:  Chose combo chart   from the list of available chart for our requirement.

Step 3: Say next and Chose your dimension, though it may look like we need two dimensional chart to achieve our requirement but really not the case, so we are going to choose only one dimension. If we choose the second dimension as length of the movie, stacked bar and Line will not work together, So Let’s Choose only one Dimension from here as shown below.

Step 4: Say next to work on the expressions. Our requirement is to show the count of Movie by its length as a stacked bar and line as the average rating of the movie.

Distinct Value in my length of the movie (LENGTH_RANGE) column is 5 rows (i.e.)  my Second dimension.

So i`ll be creating 5 expressions to get the count of movie for each of my Value in ‘Length Range’ Column as shown below.

 Expression 1 - Label this as ‘< 1hr ’        :  Sum ({<[Length Range] = {"< 1hr"}>} FilmCount)

 Expression 2 - Label this as ‘1 to 1.5hrs ’  :  Sum ({<[Length Range] = {"1 to 1.5hrs"}>} FilmCount)

 Expression 3 - Label this as ‘1.5 to 2hrs ’  :  Sum ({<[Length Range] = {"1.5 to 2hrs"}>} FilmCount)

 Expression 4 - Label this as ‘2 to 2.5hrs ’  :  Sum ({<[Length Range] = {"2 to 2.5hrs"}>} FilmCount)

 Expression 5 - Label this as ‘3hrs+ ’        :  Sum ({<[Length Range] = {"3hrs+"}>} FilmCount)

Expression Tab :

Once we created the Expression then Change Expressions ‘Display Options ‘ to Bar, default will be Line

 

Step 5 : Once the above steps are performed, then  Go to ‘Style’ tab and Change your ‘Subtype’ to ‘Stacked’

                              

Step 6: Say ok to see your stacked chart, Very Excited to See your Chart ;)?? We are still pending with one more step to add our line but still let’s go back and see what ever we have done till now makes sense.

Will see a Stacked bar chart as below.

                     

 

Step 7: Now our Stacked bar is ready, so will continue adding the expression for line in our chart. Just Go back and add one more expression for Average of Rating

Expression #6 - Label this as ‘Avg Rating’                 :      Avg (Rating)

Your final Expression tab should be something like below.

Change the expression #6 (Avg Rating) to Line in Display Option.

 

Step 8: Say ok and come out, you`d see a chart with Stacked bar with line.

                 

Only drawback of the above approach is that, if new value list is getting added in my length of Range column, QlikView will not understand to add that new value list as new expression, we will have to add those manually. Apart from that Graph should work without any issue.

Hope this post helps atleast few Qlikers on this QlikView World and I will be happy to correct if any of my above information is misleading or wrong.

Sample Application is attached with this post, have a look and let me know your thoughts!!

Thanks for reading the post and Happy Qliking!!!