Skip to Content
Author's profile photo Former Member

Concatenating Values of a column

***** NOTE In 03/01/2016 *********

If your requirement is to get the list of values of a dimension [dim] , use ReportFilter([dim]). This will give you the LOV of [dim] , separeted by “;”.

If you need the list unfiltered use NoFilter(ReportFilter([dim])).

Regards,

Rogerio

Hi,

Sometimes we face a requirement of concatenating values from an object., like below screen :

/wp-content/uploads/2014/11/concatenate1_595904.png

This can be achieved with 3 variables :

[conc] , a dim variable

[conc] = ([State]+”,”+Previous(Self));

[final] a measure variable

[final] =Last ([conc] ForEach ([State])) In Report

[No_ending_comma] a measure variable

[No_Ending_Comma] = Left([Final];Length([Final])-1)

How does it work :

The [conc] variable, will concatenate the value of [State] and the previous value of [conc], since the Previous value of ‘California’ doesn´t exist, it returns null and the the first row of [conc] is justa California. Refer to the above image, column [conc] , to see the values of [conc] as [State] is shown.

The  second variable [final] is where the ‘trick’ works :

It takes the last values of [conc] in the report calculated in the context of [State]. I use the ForEach operator so I can get the last value independent of the context used.

This example shows the values concatenated in descending order. To get it in ascending order, use [conc_asc] = (Previous(Self)+”,”+[State])

[final_asc] = =Last ([conc] ForEach ([State])) In Report

[No_ending_comma_asc  =  Rigth([final];Length([final]-1)

And what about the following cenario :

The requirement is to concatenate the stores name for each state

Here, I queried e-fashion for [State] and [Store Name]

/wp-content/uploads/2014/11/concatenate2_595905.png

I altered [conc] to :

=(Previous(Self;([State]))  ForEach([State];[Store name]) +”,”+([Store name] ForEach([State];[Store name])))

and

[final] to :

=Last ([conc]ForEach([State];[Store name])) In ([State])

Cheers,

Rogerio

the stores name for each state

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Very nice!. Thanks for sharing.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      thank you Marek!!

      Author's profile photo Former Member
      Former Member

      Just what I needed. Ta

      Author's profile photo Andrea Molina
      Andrea Molina

      Hi Rogerio Plank . Thanks for your post. It helped a lot.

      Just one question. I need to show just the top 3 data and I'm using ranking and it is not working. I get the 3 mixed. Any idea why?

      Thanks,

      Andréa

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Andrea,

      thanks for your comment.

      Can you give me an example in a Excel file, so I can try working it out ?

      Regards,

      Rogerio

      Author's profile photo IDA GHIRARDI
      IDA GHIRARDI

      Hi,

      this is exactly what I need.

      when implementing conc variable as dimension, I get #MULTIVALUE result.

      I'm running DESKI v 12.7.0.1983 (an old version indeed)

      Thx,

      Ida