Do I need some form of Pivot Table?

[#1]

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.

[#2 · Accepted solution]

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.

[#3]

Thats Fantastic NMiller - thank you ever so much!

While the solution does work I wonder if anyone can think of a way of doing this dynamically . That would be great as an idividual can have 15 + accounts each with 20+ attributes. This would be time consuming to set up and modify.

[#4]

You could try adding AcctOrder as a dimension on a pivot chart and then put AcctOrder across the top. That would expand for however many accounts you could have and simplify the expressions, but you're stuck with the AcctOrder column across the top.

Here's an updated example.

[#5]

That I Like ! The Column at the AcctOrder across the top doesn't bother me - in fact it will be slightly usefull.

Thanks again.