topic Re: Distinct values from different columns in QlikView

Distinct values from different columns

Mon, 06 Jan 2014 05:34:34 GMT

Hi ,

I have a table in database like this

ZoneId	regionid	districtid	territoryid	TTerritory	TDistrict	TRegion	TZone
52	1207	104	133	0	0	215550	475879
52	61	114	134	0	7182	172362	475879
52	61	102	134	0	0	172362	475879
1206	66	122	240	0	29560	233082	247206
1206	66	92	136	0	34129	233082	247206
1206	66	92	136	0	34129	233082	247206
1206	66	92	136	0	34129	233082	247206

i have 4 list boxes Zone,Region,District and Territory in my dashboard,if i will select Zone in the list box the distinct value will display in the pivot table from TZone column in the database.

Like this if i will select Region in the list box,the distinct value will display from TRegion column in the database and so on....

I wrote the expression in pivot table like this:

=(if(GetFieldSelections(Zone)>=1,sum(distinct{$<flag={'mrktshare'},CommonYear={$(VmaxYear-1)},CommonMonthNo={$(VmaxMonth)}>}TZone),

if(GetFieldSelections(Region)>=1,sum(DISTINCT{$<flag={'mrktshare'},CommonYear={$(VmaxYear-1)},CommonMonthNo={$(VmaxMonth)}>}TRegion),

if(GetFieldSelections(District)>=1,sum(DISTINCT{$<flag={'mrktshare'},CommonYear={$(VmaxYear-1)},CommonMonthNo={$(VmaxMonth)}>}TDistrict),

if(GetFieldSelections(Territory)>=1,sum(DISTINCT{<flag={'mrktshare'},CommonYear={$(VmaxYear-1)},CommonMonthNo={$(VmaxMonth)}>}TTerritory),1

)))))

It is working for Zone selection after i will select Region still it is showing Zone Value,if i will deselect the Zone it is showing Region Value.

How can i achieve this in QlikView based on selections in the list box calling the distinct values in different columns.

I am struggling past from 4 days,can some one guide me in this how to achieve this..

Regards,

baru

Re: Distinct values from different columns

Mon, 06 Jan 2014 06:14:11 GMT

Hi All,

Can some one help me in this........

Regards,

baru

Re: Distinct values from different columns

sureshbaabu — Mon, 06 Jan 2014 07:17:15 GMT

Hello,

Can you try creating multiple expressions(one for each) and display the appropriate based on 'Conditional' option

Thanks

Re: Distinct values from different columns

tresB — Mon, 06 Jan 2014 07:18:29 GMT

In a first look, GetFieldSelections() returns a string, I guess you have to use getselectedcount() instead.

Re: Distinct values from different columns

Mon, 06 Jan 2014 07:30:40 GMT

Hi tresesco,

thanks for your reply,

i tried getselectedcount() also, but it's not working.can i have some alternate way to do this...

Re: Distinct values from different columns

Mon, 06 Jan 2014 07:32:29 GMT

Hello Suresh,

i have 8 conditions form 8 tables,so i looking to do this in one expression .

Re: Distinct values from different columns

tresB — Mon, 06 Jan 2014 07:33:31 GMT

Could you post a sample app that demonstrates the issue?

Re: Distinct values from different columns

sureshbaabu — Mon, 06 Jan 2014 07:35:02 GMT

just try the following:

expression 1:

Conditional: = GetSelectedCount(ZoneId)=1

Definitional: = sum( DISTINCT TZone)

expression 2:

Conditional: = GetSelectedCount(regionid)=1

Definitional: = sum( DISTINCT TRegion )

Thanks

Re: Distinct values from different columns

Mon, 06 Jan 2014 07:55:18 GMT

Hi Tresesco,

Thanks for the reply,

getselectedcount() is working but we need to pass this in every condition.so that it will work...

Re: Distinct values from different columns

Anonymous — Mon, 06 Jan 2014 08:24:52 GMT

Can you please share your qvw with some sample data.....