Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III
Creator III

List box as table

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

10 Replies
techvarun
Specialist II
Specialist II

You may try the attached.

Derived from

QlikView App: Multi Column List Box

jessica_webb
Creator III
Creator III
Author

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!

kusumanchir
Creator
Creator

Hi Jessica,

Please find the attached and let me know your opinion.

jessica_webb
Creator III
Creator III
Author

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

kusumanchir
Creator
Creator

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.

jessica_webb
Creator III
Creator III
Author

Hi Rasly,

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

Thanks for your help!

effinty2112
Master
Master

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 ASection BSection CSection DSection ESection GYear
1UKSwindonBikeE54CitizenCitizen Anon1
2NewcastleCarB412
3CardiffQ353
4EdinburghS74
5BristolN14
6LiverpoolE41
7BrightonE86
8Y24
9S72
10Q32
11N25
12R43
13Q21
14H47
15J65
16Z25
17T27
18R82
19W23
20F67
21A59
22T43
23L24
24D53
25A53
26V14
27D85
28Z68
29Y77
30C43

Cheers

Andrew

jessica_webb
Creator III
Creator III
Author

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

effinty2112
Master
Master

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 ASection BSection CSection DSection ESection G
11 UKSwindonBikeE54CitizenCitizen Anon
2 NewcastleCarB41  
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  
21 UKSwindonBikeE54CitizenCitizen Anon
2 NewcastleCarB41  
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  
31 UKSwindonBikeE54CitizenCitizen Anon
2 NewcastleCarB41  
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