topic Re: Concatenate Rows in QlikView

Concatenate Rows

Thu, 13 Feb 2014 10:00:20 GMT

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

Re: Concatenate Rows

rbecher — Thu, 13 Feb 2014 10:02:33 GMT

Hi Adeel,

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

- Ralf

Re: Concatenate Rows

MK_QSL — Thu, 13 Feb 2014 10:04:55 GMT

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.

Re: Concatenate Rows

sunilkumarqv — Thu, 13 Feb 2014 10:08:39 GMT

use cross table in your script edtior itshelp you ..

see sample.

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

Re: Concatenate Rows

ashwanin — Thu, 13 Feb 2014 10:10:35 GMT

Hi Adeel,

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

refer the qv attached for this.

Re: Concatenate Rows

Thu, 13 Feb 2014 10:28:36 GMT

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.

Re: Concatenate Rows

Thu, 13 Feb 2014 10:33:20 GMT

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.

Re: Concatenate Rows

rbecher — Thu, 13 Feb 2014 10:44:24 GMT

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

Re: Concatenate Rows

Thu, 13 Feb 2014 10:49:05 GMT

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

Re: Concatenate Rows

MK_QSL — Thu, 13 Feb 2014 11:01:13 GMT

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.

Re: Concatenate Rows

er_mohit — Thu, 13 Feb 2014 11:07:05 GMT

on expression side

aggr(concat(Device,','), [User ID], Name)

Re: Concatenate Rows

Thu, 13 Feb 2014 11:08:02 GMT

Thank You Manish for your time.

Means you say if we want to extract via script it will always take time to reload. no help on this..

Ok Thank you for all

Re: Concatenate Rows

MK_QSL — Thu, 13 Feb 2014 11:10:39 GMT

Reload time will depends on your Data. Here your have 10M rows and due to grouping it is taking more time.

But if you use the same CONCAT function in Table/Chart instead of Script, it will take more time for everytime you select/deselect your dimensions.

Re: Concatenate Rows

Thu, 13 Feb 2014 11:17:03 GMT

Exactly that's why we need to work on script rather then use Table/Chart but as you say it will always take time due our data we can't do anything on it....

Re: Concatenate Rows

rbecher — Thu, 13 Feb 2014 11:20:35 GMT

But you could limit UI activities with conditional views etc. For instance, show only the concatenated devices if one user (or something else) is selected. From my point of view it makes no sense to lose details and do this transformation on script.

Re: Concatenate Rows

Thu, 13 Feb 2014 11:45:41 GMT

Becher,

Conditional view is for just hide or show the chart not limit the records..

If you look out the data we are not losing any detail we have all record in the data in the form of 1 row.

Re: Concatenate Rows

rbecher — Thu, 13 Feb 2014 11:51:11 GMT

I meant it is not a problem to have may records. It is a problem if you provide a wrong UI.

And, how do you count Devices then if you want to know it for some aspects?

Re: Concatenate Rows

Thu, 13 Feb 2014 12:04:51 GMT

If we want to count devices, simple we count ","+1 in Devices because it separate the devices

Re: Concatenate Rows

rbecher — Thu, 13 Feb 2014 12:19:01 GMT

Interesting. Tell me how you wanna do this? Do you think string operations are faster than counting field values??

Re: Concatenate Rows

Thu, 13 Feb 2014 12:38:06 GMT

Ralf,

First of all we don't want to count No of devices once we need this i prefer to find Length of the field and work with that because Len function is much faster then Count But if we need to count No. of devices but we dont want that.