Getting Started With QlikView

Today I am going to introduce you to a BI tool QlikView. For that you need to have QlikView 12 installed in your computer and example code files(data files).

You can find QlikView here: http://www.qlik.com/products/qlikview

You can find example codes here: https://www.packtpub.com/books/content/support/10459

Step-1: You need to create workspace where you are going to save your analysis. We are going to create a analysis on Airlines Operations so I’m creating folders like this:-

1

STEP-2: Now open it by double clicking its icon (You can find it in desktop or you can go to the directory where you have installed).

STEP-3: Now click on new to create a new application and save the application as Airline Operations.qvw inside Airline Operations/Apps.

 2

 STEP-4: Now open Edit Script by selecting File>Edit script as shown in screenshot marked by red circle followed by red box or by pressing Ctrl + E. It initially has 10 lines of code, all starting with the word SET. Those are the initialization variables for some common formatting options. We will leave them as they are for now.

3

STEP-5: At the bottom of the script editor, we will see a set of tabs containing specific functions regarding script generation. Make sure the Data tab is active and mark the Relative Paths checkbox, as shown in the screenshot marked inside the blue box.

STEP-6: click on the Table File marked inside the yellow box button to bring up the Open Local Files wizard. Browse to the Data Files\QVDs folder we created in the previous section and select the Flight Data.qvd file, as shown in the following screenshot. Click on Open.

4

STEP-7: The File Wizard dialog now appears. The File Type option will be set to Qvd (on the left pane) automatically, as shown below. Click on Finish to close the window.

5

STEP-8: The next thing we’ll do is assign an internal name to the loaded table and call it Main Data. To do this, type [Main Data]: (don’t forget the colon) right above the Load statement. See the Purple box to see the position of main data statement.

6

STEP-9: We will now reload the script for the data to be loaded into the QlikView document so we can start working with it. However, before we do that, it’s a good practice to hit the Save (Marked by in above screenshot Red Circle) button so we do not lose the changes if the script execution goes wrong. After saving the file, locate the Reload (Marked by in above screenshot Black Circle) button, shown in the following screenshot, in the toolbar at the top and click on it.

STEP-10: After the script execution, the Sheet Properties window will appear. From the Available Fields list on the left, add the Carrier Name, Origin City, Origin Country, Origin State, Destination City, Destination Country, and Destination State fields to the Fields Displayed in List boxes list on the right by highlighting each of them and clicking on the Add> button. Click on OK to apply the changes.

You can also bring up the Sheet Properties dialog window by right-clicking in a blank space inside the sheet area, then selecting Properties… from the context menu. Once the Sheet Properties window is open, make sure the Fields tab is active.

7

STEP-11: Go to the Edit Script window (Ctrl + E) and position the cursor on the line directly above the name we assigned to the first table ([Main Data]). Then, go to the Tab menu and select Insert Tab at Cursor…. The Tab Rename Dialog window will appear, in which we will type Main Data, to name the new tab, and click on OK. The code we generated previously will be moved to this new tab.

8

STEP-12: Activate the tab on the far right, which should be the one named Main Data, and select Tab | Add Tab…. In the Tab Rename Dialog window, type Airlines and click on OK.

STEP-13: Click on the Table Files… button and browse to the Carrier Groups.qvd file located in the Data Files\QVDs folder. Highlight it and click on Open.

STEP-14: The Qvd file type should automatically show as selected in the left pane of the File Wizard: Type window. Click on Finish to close the dialog window.

STEP-15: Remove the Directory; instruction and assign a name to the table by typing [Carrier Groups]: right above the Load statement.

STEP-16: Take a moment to follow steps 12 through 14 for the remaining tables, which are listed below, but assign a different table name to each of them in step 14(Same as there file names).

STEP-17: We will add the tables contained in the following files to the following tabs:

  • Airlines tab:
    • qvd
    • Carrier Operating Region.qvd
    • Flight Types.qvd
  • Aircrafts tab:
    • Aircraft Groups.qvd
    • Aircraft Types.qvd
  • Airports tab:
    • Distance Groups.qvd

STEP-18: After adding these tables and reloading the script, press Ctrl + T to bring up the Table Viewer window, which shows the newly constructed data model

9

STEP-19: Now we will add a new sheet and name it Dashboard.

STEP-20: Right-click on a blank space of the sheet area and click on Select Fields…. Then, add the following fields to allow filtering: Year, Quarter, Month, Carrier’s Operating Region, Carrier Group, Aircraft Group, and Flight Type. After adding the specified fields, click on OK.

STEP-21: Now we have to right click on each table and select properties, then go to Presentation tab. At the Presentation tab Adjust the following settings:

  • Set the Alignment to Center for both Text and Numbers.
  • Uncheck Single Column.
  • Mark the Fixed Number of Columns checkbox and
    • set it to 3 in the Year field
    • Set it to 2 in the Quarter field
    • Set it to 6 in the Month field
    • Set it to 3 in the Carrier’s Operating Region
  • Mark the Order by Column
  • Click on OK to apply the changes.

10

STEP-22: Now insert Search Object from New sheet objects.

111

STEP-23: Now right click on the sheet an select New sheet object and insert a  Chart. The Create Chart wizard will appear. In the Window Title field, enter Traffic per year. From the Chart Type section, select the Bar Chart option (the first one to the left) and click on Next.

11

STEP-24: From the list on the left, locate and highlight the Year field and add it to the Used

Dimensions list by clicking on the Add > button. After that, click on Next, as shown

in the following screenshot:

12

STEP-25: After clicking next Edit Expression dialog will open. There we need to write the expression for total number of flights and click ok.

Sum ([# Departures Performed])

13

STEP-26: Now go to the chart properties and adjust followings :

  • From the Caption tab, uncheck the Show Caption
    • The Caption tab is the right-most tab in the Properties You might need to use the slider buttons at the top-right corner to make it visible.
  • From the Number tab, select Integer as the number format.
  • From the Axes tab, enable the Show Grid checkbox from the Expression Axes section (the one at the top, since there are two Show Grid checkboxes).
  • Also from the Axes tab, change the Primary Dimension Labels orientation to Diagonal.

222

(One Chart & Multiple Analyses With Cyclic Expressions )

STEP-27: Now right click on the chart and go to the properties. In the Expression tab click on Add to give the following expressions to the chart individually for each of them.

Sum ([# Transported Passengers])

Sum ([# Transported Freight])

Sum ([# Transported Mail])

333

STEP-28: Now in the Expression tab from the properties, group the expression to get the cyclic table and make sure the new expressions are formatted as Integer as before

444

(A time drill-down group)

STEP-29: First, right-click on the bar chart created above and select Properties…. Activate the Dimensions tab, then locate the Edit Groups… button at the lower-left corner and click on it. The Groups dialog window will pop up. Click on the New… button and, from the Group Settings dialog window, enter Time as the Group Name, making sure the Drill-down Group radio button is selected. From the Available Fields list on the left, locate the Year and Month fields, and add them to the Used Fields section on the right by highlighting them and clicking on the Add> button. Make sure the fields are added in the correct order.

14

STEP-30: You will now see the newly created group in the Available Fields/Groups list in the Dimensions window. Highlight it and add it to the Used Dimensions list by clicking on the Add> button. Then, remove the one we previously had (Year) by highlighting it and clicking on the <Remove button. Click on OK.

16 17

555

(Top 10 Routes)

STEP-31: Start by clicking on the Create Chart button from the design toolbar. From the first dialog in the New Chart wizard, select the Straight Table icon, shown below, as Chart Type and set Window Title to Top 10 Routes. Click on Next.

STEP-32: We will add the following four expressions:

Sum ([# Departures Performed])

Sum ([# Transported Passengers])

Sum ([# Transported Freight])

Sum ([# Transported Mail])

18

STEP-33: After adding the expressions, make sure to set Total Mode to No Totals for all four of them. This is done by selecting the corresponding radio button at the lower-right corner of the window. Click on Next two times to open to the Presentation dialog window. Once there, enable the Max Number (1 – 100) checkbox and set it to 10. Click on Next three times to get to the Number dialog window and make sure to set all of the expressions to the Integer format.

19

Leave A Reply

Your email address will not be published. Required fields are marked *