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 !!!.