topic Re: List box as table in QlikView

List box as table

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

I have a chart displaying data from each of my fields. It's important that it stays as one single object as it will be used dynamically elsewhere.

'Year' is the key field that I want to show all possible related values for. However, there is no relationship between the other fields at all (at least not one that I want to show).

I've attached an excel doc, in the first tab is the way the data displays at the moment, and in the second tab is how I want it to display.

Basically, I want to remove duplicates and essentially show what would appear in a list box for each field, for each value in 'Year'.

Hope that makes sense! Any help appreciated.

Jess

Re: List box as table

techvarun — Fri, 04 Aug 2017 10:16:09 GMT

You may try the attached.

Derived from

QlikView App: Multi Column List Box

Re: List box as table

jessica_webb — Fri, 04 Aug 2017 10:32:09 GMT

Hi Varun,

Unless I'm missing something, the 'Multi List Box' in your application is just showing my data in a pivot table - which is what I don't want it to do!

Re: List box as table

kusumanchir — Fri, 04 Aug 2017 10:40:01 GMT

Hi Jessica,

Please find the attached and let me know your opinion.

Re: List box as table

jessica_webb — Fri, 04 Aug 2017 10:43:25 GMT

Thank you Rasly - that looks great!

Just trying to work out how you've done it

Have just realised it's all done in the script. Unfortunately I can't do that as I need my data model to stay the same

Re: List box as table

kusumanchir — Fri, 04 Aug 2017 10:47:47 GMT

Hi Jessica,

Ok, then without changing the model, you wanted the same to be implemented in the UI itself?

If so, I will try and get back to you if there is any luck.

Re: List box as table

jessica_webb — Fri, 04 Aug 2017 10:50:40 GMT

Hi Rasly,

That's right - preferably would like the layout to be the same as the 2nd tab.

Thanks for your help!

Re: List box as table

effinty2112 — Fri, 04 Aug 2017 15:24:19 GMT

Hi Jessica,

Try a pivot table with calculated dimension:

=ValueLoop(1,$(=Max(Aggr(FieldValueCount($Field),$Field))))

Edit: Need this dimension too - $Field

and expression

=FieldValue($Field,ValueLoop(1,$(=Max(Aggr(FieldValueCount($Field),$Field)))))

to get:

=ValueLoop(1,30)	$Field	Section A	Section B	Section C	Section D	Section E	Section G	Year
1		UK	Swindon	Bike	E54	Citizen	Citizen Anon	1
2			Newcastle	Car	B41			2
3			Cardiff		Q35			3
4			Edinburgh		S74
5			Bristol		N14
6			Liverpool		E41
7			Brighton		E86
8					Y24
9					S72
10					Q32
11					N25
12					R43
13					Q21
14					H47
15					J65
16					Z25
17					T27
18					R82
19					W23
20					F67
21					A59
22					T43
23					L24
24					D53
25					A53
26					V14
27					D85
28					Z68
29					Y77
30					C43

Cheers

Andrew

Re: List box as table

jessica_webb — Mon, 07 Aug 2017 07:33:41 GMT

Hi Andrew,

Thanks so much for this.

Is there a way to include the 'Year' field in the expressions so that possible values are evaluated for each year

I would also need to be able to restrict which fields show as I have other fields in the script that I do not want to be included in the pivot table (apologies - I probably should have mentioned this in my initial query, but was trying to keep things simple!).

Thanks,
Jess

Re: List box as table

effinty2112 — Mon, 07 Aug 2017 08:05:41 GMT

Hi Jessica,

Make your pivot table with these dimensions:

Year

=ValueLoop(1,$(=Max(Aggr(FieldValueCount($Field),$Field))))

=if($Field <> 'Year',$Field) // In the dimensions tab check Suppress When Value Is Null

Your expression is :

=FieldValue(if($Field <> 'Year',$Field),ValueLoop(1,$(=Max(Aggr(FieldValueCount(if($Field <> 'Year',$Field)),$Field)))))

Hopefully that will work for you.

cheers

Andrew

Year	=ValueLoop(1,30)	=if($Field <> 'Year',$Field)	Section A	Section B	Section C	Section D	Section E	Section G
1	1		UK	Swindon	Bike	E54	Citizen	Citizen Anon
	2			Newcastle	Car	B41
	3			Cardiff		Q35
	4			Edinburgh		S74
	5			Bristol		N14
	6			Liverpool		E41
	7			Brighton		E86
	8					Y24
	9					S72
	10					Q32
	11					N25
	12					R43
	13					Q21
	14					H47
	15					J65
	16					Z25
	17					T27
	18					R82
	19					W23
	20					F67
	21					A59
	22					T43
	23					L24
	24					D53
	25					A53
	26					V14
	27					D85
	28					Z68
	29					Y77
	30					C43
2	1		UK	Swindon	Bike	E54	Citizen	Citizen Anon
	2			Newcastle	Car	B41
	3			Cardiff		Q35
	4			Edinburgh		S74
	5			Bristol		N14
	6			Liverpool		E41
	7			Brighton		E86
	8					Y24
	9					S72
	10					Q32
	11					N25
	12					R43
	13					Q21
	14					H47
	15					J65
	16					Z25
	17					T27
	18					R82
	19					W23
	20					F67
	21					A59
	22					T43
	23					L24
	24					D53
	25					A53
	26					V14
	27					D85
	28					Z68
	29					Y77
	30					C43
3	1		UK	Swindon	Bike	E54	Citizen	Citizen Anon
	2			Newcastle	Car	B41
	3			Cardiff		Q35
	4			Edinburgh		S74
	5			Bristol		N14
	6			Liverpool		E41
	7			Brighton		E86
	8					Y24
	9					S72
	10					Q32
	11					N25
	12					R43
	13					Q21
	14					H47
	15					J65
	16					Z25
	17					T27
	18					R82
	19					W23
	20					F67
	21					A59
	22					T43
	23					L24
	24					D53
	25					A53
	26					V14
	27					D85
	28					Z68
	29					Y77
	30					C43

Re: List box as table

jessica_webb — Mon, 07 Aug 2017 12:45:33 GMT

I thought that was working as it should then, but have just spotted an issue...

Basically, it is just repeating the same values for Y1, 2 and 3.

But not all values are relevant to all years.

So for example, Section D 'Z68' is relevant to Y2 and Y3, but not Y1, so shouldn't appear under Y1...