side by side data

[Anonymous]

Hi guys,

I have the following customer data

Name	Customer ID	Account #	Open date
Mr Test	123	1234567899	01/01/2016
Mr Test	123	5568978944	01/02/2016
Sid Smith	888	8760305480	01/02/2016
Rob Roberts	444	4563218975	01/01/2016
Mr Test	123	4561233322	01/03/2016

I need to send a letter to everyone with details of all of their accounts so what I’d like to do is have everything for each customer on one line. So for example

Name	Customer ID	Account 1 #	Open date 1	Account 2 #	Open date 2	Account 3 #	Open date 3
Mr Test	123	1234567899	01/01/2016	5568978944	01/02/2016	4561233322	01/03/2016
Sid Smith	888	8760305480	01/02/2016
Rob Roberts	444	4563218975	01/01/2016

Is there a clever way that Qlikview can identify that a customer for example has three accounts and put them side by side in a table? Displaying them as columns rather than rows?

I’ve used the following in the past

=if(count(distinct([Account #]))>1,subfield(concat(distinct([Account #]),',',),',',1),)

This works fine if you only want to display just the account numbers because it looks up distinct values but it doesn’t pull through their respective open dates.

Name	Account #	Account #	Account #
Mr Test	1234567899	5568978944	4561233322
Sid Smith	8760305480
Rob Roberts	4563218975

Can anyone help?

[sunny_talwar · Accepted solution]

May be this?

I used the script to create a new field...

Table:

LOAD Name,

  AutoNumber(RowNo(), Name) as Count,

    [Customer ID],

    [Account #],

    [Open date]

FROM

[https://community.qlik.com/thread/245402]

(html, codepage is 1252, embedded labels, table is @1);

Used Count as a pivoted dimension

[Anonymous]

Genius thank you very much