Concatenate Rows

[#1]

Hi every one we have a typical requirement We have following data

User ID	Name	Device
80004022	ABDUL AZAM	868438010471186
80004022	ABDUL AZAM	868438010471186A
80001571	ABDUL AZIZ	868438010470071
80001571	ABDUL AZIZ	868438010470071A
80001571	ABDUL AZIZ	868438010470071B
80001594	AKHLAQ AHMED	868438010470147
80001594	AKHLAQ AHMED	868438010470147A
80001594	AKHLAQ AHMED	868438010470147B
80001594	AKHLAQ AHMED	868438010470147C
80001591	ASIM IQBAL	868438010471145
80001591	ASIM IQBAL	868438010471145A
80001591	ASIM IQBAL	868438010471145B
80001591	ASIM IQBAL	868438010471145C
80001705	IMRAN UL HASSAN	868438010464702
80001705	IMRAN UL HASSAN	868438010464702A
80001705	IMRAN UL HASSAN	868438010464702B
80006221	MUHAMMAD AHMED KHAN	868438010467937
80006221	MUHAMMAD AHMED KHAN	868438010467937A
80001703	RAJA TALLET IMTIAZ	868438010463480
80001703	RAJA TALLET IMTIAZ	868438010463480A
80001703	RAJA TALLET IMTIAZ	868438010463480B
80000756	RIZWAN MALIK	868438010471046
80000756	RIZWAN MALIK	868438010471046A
80000745	SHOUKAT IQBAL	868438010465857
80000745	SHOUKAT IQBAL	868438010465857A

We want to show User wise all devices in one row like below

User ID	Name	Devices
80004022	ABDUL AZAM	868438010471186, 868438010471186A
80001571	ABDUL AZIZ	868438010470071, 868438010470071A, 868438010470071B
80001594	AKHLAQ AHMED	868438010470147, 868438010470147A, 868438010470147B, 868438010470147C
80001591	ASIM IQBAL	868438010471145, 868438010471145A, 868438010471145B, 868438010471145C
80001705	IMRAN UL HASSAN	868438010464702, 868438010464702A, 868438010464702B
80006221	MUHAMMAD AHMED KHAN	868438010467937, 868438010467937A
80001703	RAJA TALLET IMTIAZ	868438010463480, 868438010463480A, 868438010463480B
80000756	RIZWAN MALIK	868438010471046, 868438010471046A
80000745	SHOUKAT IQBAL	868438010465857, 868438010465857A

Is this possible? Thank you

[rbecher]

Hi Adeel,

you can use a chart with dimensions: User ID, Name     and an expression: Concat(Device)

- Ralf

[MK_QSL]

Something like below..

I have only taken first few names...

Temp:

Load * Inline

[

User ID, Name, Device

80004022, ABDUL AZAM, 868438010471186

80004022, ABDUL AZAM, 868438010471186A

80001571, ABDUL AZIZ, 868438010470071

80001571, ABDUL AZIZ, 868438010470071A

80001571, ABDUL AZIZ, 868438010470071B

80001594, AKHLAQ AHMED, 868438010470147

80001594, AKHLAQ AHMED, 868438010470147A

80001594, AKHLAQ AHMED, 868438010470147B

80001594, AKHLAQ AHMED, 868438010470147C

];

NoConcatenate

Final:

Load

  [User ID],

  Name,

  Concat(Device, ', ') as Device

Resident Temp

Group By [User ID], Name;

Drop Table Temp;

The above example is for SCRIPT side but if you want at Expression and UI end..

You can use the same Concat (Device, ', ') in expression.

[sunilkumarqv]

use cross table in your script edtior itshelp you ..

see  sample.

Crosstable (User ID, Name) Load * from ex1.csv;

[ashwanin]

Hi Adeel,

User Concat(Device,',') in the expression.

refer the qv attached for this.

[#6]

Thank you Ralf,

But actually we want to Load our desire result from script just because of we have bulk of data and due to this data add more lines.

[#7]

Great Manish it works. But as i said earlier we have huge Data more than 10M and due to Group By function Dashboard execute in more time.

[rbecher]

But then you will lose that level of detail (granularity)..

[#9]

Hmmm...

You are right in some scenario but in our case we just need to now which User have multiple and which Devices.

and i think it will serve.

Thank you

[MK_QSL]

Dear Adeel,

Working on Script end will take more time to reload data but if you choose to work the same on

UI side, it will severely affect your processing time.

I always prefer to work on Script side more rather than on UI.

You can try to use both ways and find out which one is giving better result.