topic Re: Set Analysis issue in QlikView

Set Analysis issue

sibin_jacob — Wed, 11 Sep 2013 13:11:28 GMT

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

Re: Set Analysis issue

michael_maeuser — Wed, 11 Sep 2013 13:16:23 GMT

do you have some sample data?

Re: Set Analysis issue

Wed, 11 Sep 2013 13:58:12 GMT

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

Re: Set Analysis issue

Wed, 11 Sep 2013 14:00:27 GMT

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

Re: Set Analysis issue

Carlos_Reyes — Wed, 11 Sep 2013 14:22:28 GMT

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.

Re: Re: Set Analysis issue

sibin_jacob — Wed, 25 Sep 2013 09:21:26 GMT

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

Re: Re: Re: Set Analysis issue

Carlos_Reyes — Thu, 26 Sep 2013 00:04:57 GMT

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 !

Re: Set Analysis issue

er_mohit — Thu, 26 Sep 2013 04:43:54 GMT

Try this

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

hope it helps

Re: Set Analysis issue

sibin_jacob — Thu, 26 Sep 2013 09:50:53 GMT

Many Thanks Carlos......

This solution is working fine...