topic Re: Concatenating Multiple Values of a Field in QlikView

Concatenating Multiple Values of a Field

byrnel0586 — Wed, 26 Apr 2017 19:01:21 GMT

Hi,

I am wanting to concatenate multiple 'MRP D/C Dates' into one cell in order to only have 1 row per member in my pivot table. In my example there are only 2 dates, but there can be more than that. I have tried creating variables and combining those but that gives me errors in my calculated dimension. For instance, in the below example I would like to see '11/28/2016, 3/29/3016' in one row rather than it creating 2.

Any help is appreciated!

Re: Concatenating Multiple Values of a Field

vhespinog — Wed, 26 Apr 2017 19:08:58 GMT

Hello

Maybe

load

Concat(MRP D/C Dates,' ') as MRP D/C Dates

Resident Table;

Re: Concatenating Multiple Values of a Field

maxgro — Wed, 26 Apr 2017 19:13:56 GMT

did you try with the concat function?

concat(yourdatefield, ',')

Re: Concatenating Multiple Values of a Field

vishsaggi — Wed, 26 Apr 2017 19:22:04 GMT

= Concat('MRP D/C Dates', ',')

Re: Concatenating Multiple Values of a Field

byrnel0586 — Wed, 26 Apr 2017 19:30:38 GMT

I have tried that and am getting "// Error in calculated dimension."

Re: Concatenating Multiple Values of a Field

byrnel0586 — Wed, 26 Apr 2017 19:31:01 GMT

I have tried that and am getting "// Error in calculated dimension."

Re: Concatenating Multiple Values of a Field

vishsaggi — Wed, 26 Apr 2017 19:38:20 GMT

Can you paste your calculated dim expression here you are using?

Re: Concatenating Multiple Values of a Field

byrnel0586 — Wed, 26 Apr 2017 21:05:46 GMT

Sure. Here is a screenshot actually.

Re: Concatenating Multiple Values of a Field

vishsaggi — Wed, 26 Apr 2017 21:10:53 GMT

Can you try like:

= Aggr(Concat(DISTINCT EVENT_DATE, ','), EVENT_DATE)

Re: Concatenating Multiple Values of a Field

byrnel0586 — Thu, 27 Apr 2017 12:17:49 GMT

Thank you. I think I am getting there. I tried Aggr(Concat(DISTINCT EVENT_DATE, ','), MEM_ID) and that concatenated all of the event dates as it should. However, I am only needing to concatenate event dates where MeasureID = 'MRP' and Gap >0. I tried =if(match(MeasureID, 'MRP_MRP') and Gap > 0, Aggr(Concat(DISTINCT EVENT_DATE, ','), MEM_ID)) and it did not work. Your help is appreciated!

Re: Concatenating Multiple Values of a Field

sunny_talwar — Thu, 27 Apr 2017 12:20:25 GMT

May be this

Aggr(Concat(DISTINCT {<MeasureID = {'MRP'} and Gap = {'>0'}>} EVENT_DATE, ','), MEM_ID)

=Aggr(If(Match(MeasureID, 'MRP_MRP') and Gap > 0, Concat(DISTINCT EVENT_DATE, ',')), MEM_ID)

Re: Concatenating Multiple Values of a Field

byrnel0586 — Thu, 27 Apr 2017 12:36:55 GMT

Thank you. I tried the second expression and got no results. In the first expression there is an error around Gap = {'>0'}.

Re: Concatenating Multiple Values of a Field

sunny_talwar — Thu, 27 Apr 2017 12:38:06 GMT

My bad, replace and with a comma

Aggr(Concat(DISTINCT {<MeasureID = {'MRP'}, Gap = {'>0'}>} EVENT_DATE, ','), MEM_ID)

Re: Concatenating Multiple Values of a Field

byrnel0586 — Thu, 27 Apr 2017 12:43:07 GMT

That worked, thank you!

Re: Concatenating Multiple Values of a Field

berndjaegle — Fri, 21 Sep 2018 13:47:14 GMT

Thank you!

I am using it to generate Filter Import File for Nprinting

https://help.qlik.com/en-US/nprinting/September2018/Content/NPrinting/DeployingQVNprinting/Import-Users-Filters.htm