topic Re: expression : concatenate all possible values into a single row ? in QlikView

expression : concatenate all possible values into a single row ?

Tue, 04 Mar 2014 20:04:06 GMT

Hello,

I am doing some reporting based on some events. There is a date filter and on selecting different dates, I can get 1-15 records. For one specific field, I need to concatenate all those values into a single one, separate by comma.

So my question is that if I get many records for a certain field (Under certain filters/criteria), how can I concatenate them all into a single one.

I need to do this because I am plotting those events on google and sometimes I get more than one event on a single point. I need to show the names of all those events in the popup, and to do this, I need to concatenate them

so for example, I select a date, and I get the following

event1

event2

event3

event10

How can I dynamically concatenate these all into a single expression

Syed

Re: expression : concatenate all possible values into a single row ?

Tue, 04 Mar 2014 20:11:01 GMT

you mean simple concat(distinct fieldname,',') not working for your purpose?

Re: expression : concatenate all possible values into a single row ?

MK_QSL — Tue, 04 Mar 2014 20:11:29 GMT

Something Like

Concat(Distinct Event,', ')

Re: expression : concatenate all possible values into a single row ?

Anonymous — Tue, 04 Mar 2014 20:11:46 GMT

why not use concat function?

something like

concat(if(len(eventField)>0,eventField), ', ')

Re: expression : concatenate all possible values into a single row ?

rustyfishbones — Tue, 04 Mar 2014 20:17:32 GMT

Do you mean something like this

Try

Re: expression : concatenate all possible values into a single row ?

Tue, 04 Mar 2014 20:18:55 GMT

different values are different records..suppose I have 10 unique records ...each row containing a differnt unique event...how do I make the below string from those 10 records

Event1, Event2, Event3,.......Event10

So here I am not concatenating different fields..but I am concatenating all the records of the same column (Events)

Re: expression : concatenate all possible values into a single row ?

Tue, 04 Mar 2014 20:21:00 GMT

Yes, I mean this exactly like this. getfieldselections(Event) would give me this If it was a listbox and I selected those events

However, this is not a listbox and I want to achieve the same without using getfieldselections as I want to get those dynamically without selecting them in the listbox

Syed

Re: expression : concatenate all possible values into a single row ?

rustyfishbones — Tue, 04 Mar 2014 20:23:21 GMT

I just selected them for emphasis

use

CONCAT(DISTINCT Field,',')

Re: expression : concatenate all possible values into a single row ?

Tue, 04 Mar 2014 20:26:05 GMT

Thank You All. You were all correct. I had not used this function before and I did not know that this function concatenates all the values of a column. This worked exactly the way I needed it to do

Arif