4 Replies Latest reply: Oct 12, 2010 1:25 PM by saulthomas RSS

    Do I need some form of Pivot Table?

       

      Person ID

      Account Number

      Open Date

      Closed Date

      Account Number

      Open Date

      Closed Date

      Account Number

      Open Date

      Closed Date

      repeated -->

      a1

      123

      01-Apr-09

      01-Jun-10

      234

      05-May-08

      01-Jun-10

      345

      05-Jun-06

      01-May-10

       

      a2

      321

      02-Apr-10

      02-Jun-10

      432

      06-May-08

      -

      -

      -

      -

       

      a3

      111

      03-Apr-10

      -

      -

      -

      -

      -

      -

      -

       

      a4

      222

      01-Dec-10

      -

      333

      10-Nov-07

      02-Mar-10

      -

      -

      -

       

      a5

      999

      02-Dec-10

      -

      888

      10-May-05

      03-Mar-10

      777

      05-Jun-06

      01-May-10

       



       

       

       



       

      I would like to show a table similar to the above where one individual (Person ID) can have more than one Account (Account Number) with some attributes (Closed Date, Open Date etc) with each of the accounts shown along one row. Individuals may have one or more accounts.

       

      I wasn't quite sure as to the subject of the posting - I believe that I may need some form of Pivot table but without any formal Qlikview training I am in need of some guidance. Any Ideas anyone?

       

      Thanks in Advance,

      Saul.

       



       

       

        • Do I need some form of Pivot Table?
          Neil Miller

          I wouldn't say you need a pivot table, because neither a pivot table or a straight table is going to be able to do this off the shelf. In order to set something like this up, you will probably want to modify your load data to make it easier. For example, you could have a field called AccountOrder and make it an integer going from one to how ever many accounts they have. Then in the chart, for the first one you could do:

          Max({<AccountOrder = {1}>} AccountNumber)
          Max({<AccountOrder = {1}>} OpenDate)
          ...


          It may be a better idea to use Only instead of Max, but you need an aggregate function to put your Set Analysis and you need your data to allow you to select the appropriate record to place in each column.

          This chart is not trivial to create in QlikView, but if you set up your data correctly, it shouldn't be overly difficult.

          EDIT: I posted a sample. In the sample, you must define expressions up to the max number of accounts you would like to display. I don't know if there is a way to make it dynamic.