2 Replies Latest reply: Jan 6, 2017 5:58 AM by Paul Wonford RSS

    side by side data

    Paul Wonford

      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?