CONCATENATE help with weird table structure

Lucinda_Mason · 05-27-2020 01:37 PM

I have a legacy table with a strange data structure. I’m trying to make a virtual column but can’t get it to work. I don’t know if it because of the GEO table structure or simply because [Country_Geo_NO] can be a list, not just a single value. Let’s assume that is the issue.

This is in another table. There can only be ONE Geo_NO per request. The [Country_Geo_NO] is based on the Geo_NO. There can be one or MORE [Country_Geo_NO] per request. I think that is where at least part of my problem is.

The expression below:

only shows ONE [Country_Geo_NO], even when there are multiple.
only shows the number and not the name. I’ve tried multiple ways but can’t get the [Geo_Name] to show

CONCATENATE([Geo_NO].[Geo_Name], " – ", [Country_Geo_NO])

Thanks for pointing me to the right direction.

Bahbus

Even though I don’t think this will work, try:

[Geo_NO].[Geo_Name] & " – " & CONCATENATE([Country_Geo_NO])

My thought is that there might be a bug with CONCATENATE and this, if it does work, would expose it.

Lucinda_Mason

Thanks. I must have missed something.

All three of these now produce the list of countries when there are more than one.

CONCATENATE([Geo_NO].[Geo_Name], " – ", [Country_Geo_NO]) * this is the original one that I thought wasn’t showing all the countries.

CONCATENATE([Geo_NO].[Geo_Name], " – ", LIST([Country_Geo_NO]))

[Geo_NO].[Geo_Name] & " – " & CONCATENATE([Country_Geo_NO])

Is one of these more efficient than the others?

Now I need to figure out how to get the reference or dereference to Geo_Name from Country_Geo_Name.
This is how I create the EnumList for Country_Geo_NO:
IN( [_THIS], SELECT(GEO[GEO_NO], [_THISROW].[GEO_NO] = [REGION MATCH]))

This is the table structure for Geo:

Bahbus

CONCATENATE() is just a fancy form of using the & symbol, I believe. So they should all be basically equal.

For that you’ll need to replace [Country_Geo_NO] with a Select statement to get the column data you want.