Set Analysis issue

[sibin_jacob]

Hi,

I have two expressions in my straight table. in this first expression I am getting GEOID and the second expression I need to get the corresponding city name based on GEOID and the dimension.I can't give GEOID as dimension. My dimension is CustermerID.

I have multiple GEOID and CITY for each CustermerID(Dimension) but I need to show only one GEOID and the corresponding CITY for each customerid.

expression for GEOID

(concat(%Issp_Geography_Dim_Id,'|'),'|',1)

expression for CITY

({$<%Issp_Geography_Dim_Id={$(=SubField(concat(%Issp_Geography_Dim_Id,'|'),'|',1))}>}City)

I am getting correct GEOID using this expression. But I am not getting the CITY value properly. I will get correct city name if I select one customer from the table. Also how to use expression label as a condition in set analysis.

Thanks,

Sibin Jacob. C

[michael_maeuser]

do you have some sample data?

[#3]

You can add the Calculated Dimension for GEOID and use the CITY as expression .

[#4]

You can use the Set Analysis only in AGGR function.

use CONCAT({$<%Issp_Geography_Dim_Id={$(=SubField(concat(%Issp_Geography_Dim_Id,'|'),'|',1))}>}City,'|') so you can get List of all cities corresponding to GEOID

[Carlos_Reyes]

If you have multiple GEOID and City for each CUSTOMERID, which is the criteria to know the corresponding GEOID and CITY ?  It'll be easier to help if you upload some sample data.

[sibin_jacob]

Hi all,

PFA my sample application and data file.

I need only one record for each cusomerID. If one customer is in more than one GEOID, I need to show first GEOID based on ascending order and the corresponding CITY of GEOID and Customer.

In this sample application, I created CustomerID as dimension and GEOID,CITY as expressions but I am not getting the correct CITY.

Please let me know if you need any more details.

Thanks,

Sibin Jacob.C

[Carlos_Reyes · Accepted solution]

You just needed to add the ORDER parameter in the concat funcion. Check the table " Test 1 " . Of course... that will give you only the smallest CITY, according to its GEOID, based on the current selections. So if you need to always get the smallest, disregarding selections... you may add a " {1} " to the concat function.

Hope it helps !

[er_mohit]

Try this

Concat({$<%Issp_Geography_Dim_Id={"$(=SubField(concat(%Issp_Geography_Dim_Id,'|'),'|',1))"}>}City)

hope it helps

[sibin_jacob]

Many Thanks Carlos......

This solution is working fine...