8 Replies Latest reply: Nov 5, 2012 5:04 PM by Torunn Midtsjo RSS

    Pivot table or straight table?

    Torunn Midtsjo



      I have a problem with showing some information in a dashboard.   I want to show information from 2 tables in the same table in a dashboard.


      We have a big data modell with many tabels loaded from MS-SQL into qvd-files.  My need is as follows:


      One table named "OCMQM1" contains information about incoming orders. Example of fields:

      OCMQM1.Number = unique reference for the order  (Q1001)

      OCMQM1.Company = name of the company

      OCMQM1.Open_time = when the order were registered



      The orders are often so complicated that they have to be divided into sub-orders.  We have a table called "OCMLM1" that contains information about sub-orders. One order can have many sub-orders.   Example of fields:

      OCMLM1.Number = unique reference for this suborder (Q1001-01)

      OCMLM1.Company = name of the company

      OCMLM1.Open_time = when the suborder were registeret

      OCMLM1.Parent_order=Reference to the main order in "OCMQM1



      The table for sub-orders has a field with reference to the "OCMQM1"-table, so there is no problem to link this two table together (OCMLM1.parent_order are linked to OCMQM1.number).


      My need is to show information about both the main-order and the belonging sub-orders in the same table in QlikView (if possible). I shall not do any calculations, but only show fields from both the tables.  I need to use expressions tho select which orders to show.   I know it is a possibility to have 2 tables, but the best solution is to show all information in one table.


      I want to show something like this in the dasbboard (orange fields are from table OCMQM1 and green fields from table OCMLM1):


      Main-order  Open-time     Company     Assigned-to     Status     Sub-order     Assigned-to          Status

      Q1001          12.11.01          XXXXX     IT-RT-MAIL     Open      Q1001-01     IT-RT-Network      Pending

                                                                                                          Q1001-02      IT-RT-SAN           Open

      Q1002          12.11.02          XXXXX     IT-RT-SAN      Open     Q1002-01      IT-RT-Mail             Closed


      I have 2 dimensions (order and sub-order), that shall show information from 2 different tables.  I suppose the solution is very simple, but I have been testing with pivot-table without finding a solution. I have such a pivot-table working fine in Excel, but I have problems with fixing it in QV. 


      I am grateful for any help with this problem.  Must I use something like "Dimensionality"?


      Best regards


        • Re: Pivot table or straight table?
          whiteline _



          You should load your data in a right way. I've mentioned that there is no problem to link the tables.

          Have you linked them in QV ?

          (QV links tables by similar field names)


          You can have dozens of tables in QV data model and use different fields from different tables in one chart (pivot).


          You should also have to make some expression to see the rows (pivot or straight table).

          Try =count(OCMLM1.Number) for example.

            • Re: Pivot table or straight table?
              Torunn Midtsjo

              Hi again!


              Yes, we have linked them in QV. 


              I can see the rows from both tables when I am testing, but I am not able to create a table that shows both main-order information and information about all the associated sub-orders in the same table. The first columns in the table shall show info about the main-order and the rest of the columns info about each sub-order.  We can have up to 15 sub-orders for some orders.


              Shall the unique id-field to the main order and the unique id-field to sub-orders be dimensions?   The pivot table works fine if I only select these two fields as dimensions and select other fields from the sub-orders as expressions.  How can I show several fields from the main-order, before the info about sub-orders are shown? Must these fields also be dimensions?


              I have a feeling that the solution is very simple, but I am not able to see it.


              Best regards