Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
pzwonford
Contributor II
Contributor II

side by side data

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?

1 Solution

Accepted Solutions
sunny_talwar

May be this?

Capture.PNG

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

View solution in original post

2 Replies
sunny_talwar

May be this?

Capture.PNG

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

pzwonford
Contributor II
Contributor II
Author

Genius thank you very much