topic Re: concatenate multiple rows into one in QlikView

concatenate multiple rows into one

Thu, 06 Nov 2014 21:50:15 GMT

My problem is that I need to concatenate multiple rows into one row

This is my original table

Job Name	resource name	% Complete	Duration
Levantamiento de informacion	Angelica Leon	100%	24 horas
Levantamiento de informacion	Jorge Morua	100%	24 horas
Entrega de Informacion	Angelica Leon	75%	120 horas
Diseño Funcional	Angelica Leon	100%	48 horas
Diseño Tecnico	Jorge Morua	100%	4 horas
Desarrollo/Programacion	Jorge Morua	95%	32 horas
Configuraciones	Jorge Morua	80%	56 horas
Pruebas Unitarias	Jorge Morua	0%	8 horas
Ajustes	Jorge Morua	0%	8 horas
Pruebas Integrales	Angelica Leon	0%	3 horas
Pruebas Integrales	Jorge Morua	0%	3 horas
Pruebas Integrales	Aide pineda	100%	4 horas
Pruebas de Usuario	Aide pineda	0%	8 horas
Ajustes Pruebas	Jorge Morua	0%	8 horas
Documentacion	Jorge Morua	0%	8 horas
Formacion	Jorge Morua	0%	2 horas
Salida a Produccion	Jorge Morua	0%	1 hora
Soporte de Garantia	Jorge Morua	45%	40 horas
Liberacion	Jorge Morua	0%	1 hora
Liberacion	Angelica Leon	100%	2 hora

I have to show the values of the "Resource Name" column in a single row in a pivot table, for this, is required that the value of the "Resource Name" column must concatenate each value to be repeated in the column "task Name ", for example:

Job Name	resource name
Levantamiento de informacion	Angelica Leon,Jorge Morua
Pruebas Integrales	Angelica Leon,Jorge Morua,Aide pineda
Levantamiento de informacion	Angelica Leon
Diseño Tecnico	Jorge Morua

could you tell me if there is any function to do this or how I could fix it?, thanks in advance!!!

Re: concatenate multiple rows into one

vgutkovsky — Thu, 06 Nov 2014 21:56:02 GMT

Hi, Jorge. Use function concat(). Which takes 3 parameters. In your case, you would write something like this: concat(distinct [resource name],',',[resource name])

The above assumes you want to order the concatenation alphabetically. If you need to use a different order, change the 3rd parameter.

Cheers,

Vlad

Re: Re: concatenate multiple rows into one

Thu, 06 Nov 2014 23:05:36 GMT

Thanks Vlad, it works.

I use this function as an expression in the pivot table: concat([resource name],', ', [Job Name])

but I needed the new field as a dimencion so I use this code

load Nombre_Tarea,

Concat(Nombre_Recurso,', ',Nombre_Tarea) as Union

Resident Pruebas

group by Nombre_Tarea;

and it worked well, thanks for your help !!!!

Re: concatenate multiple rows into one

kbhasinptc — Thu, 20 Dec 2018 09:40:58 GMT

Can we use this function for a normal table not pivot table

Re: concatenate multiple rows into one

PrajS — Tue, 28 Jul 2020 06:23:01 GMT

Yes you can do it, provided you add the column as measure and not dimension