A Better Ad Hoc Listing

larry_w_ · Sep 21, 2022 5:11:51 PM

The attached application shows you how to create a data-driven Ad-Hoc listing just using variables. Using a data-driven approach you can also "copy" dimensions and expressions to an Ad-Hoc listing so the user can add additional columns.

An Ad-Hoc listing is a table where the user can choose what dimensions and expressions from the data model are displayed.

This is a very powerful means of examining the data.

Customarily in QlikView this has been done by creating a straight or pivot table and adding all possible expressions and dimensions.

Conditional expressions are added to each dimension and expression so that when the user selects a dimension or expression name from a list box, the associated columns are made visible in the table. This approach has a big drawback: the columns pop up wherever they were defined, not in the order selected. Needless to say this looks very unprofessional. The user shouldn't have to move columns around to fix their listing which was correct when they entered it.

It would be great if an Ad-Hoc listing could be created which preserves the order of selections. To make this data-driven would be an additional plus. This could be done with macros, dynamically creating a table using the API. But macros have a number of drawbacks and essentially Qlik's position is that they should not be used.

The attached application shows you how to create a data-driven Ad-Hoc listing just using variables without any macros.

This approach preserves the order dimensions and expressions appear for a more polished listing.

The user can create an Ad-Hoc listing and then save the listing in a bookmark for later use.

In addition, you can attach icons to every graph and table in your application which will allow the user to apparently "copy" the dimensions and expressions used into an Ad-Hoc listing for detailed analysis.

I'll just provide a cookbook approach to just getting an Ad-Hoc listing working without going into how it's done.

If there's any interest I'll provide some explanation of how it works.

Download the sample application and associated spreadsheet and place them together in the same folder

Explore the application to see how the Ad-Hoc listing works and how a chart can "copy" dimensions and expressions to the listing

To create an Ad-Hoc listing for your own application, see the recipes in the attached document.

buzzario · ‎2019-04-24

Hi Larry,

I really like your app, and how it is so customizable from the excel without much work in the front end. Now I'm trying to implement this with my customer, and to literally copy and paste your app is no problem. However, they have 50+ dimensions they want dynamically, which is a bit too much to be easily workable in a list box. So my "boss" and I decided that we want to give each column a classification, with a max of 10 classifications which fit nicely into a small list box without scroll bar.

For example we have 6 dimensions we classified under the category 'Basic'. If we select the category 'Basic' in the list box with categories, we want the 6 dynamic dimensions which are linked to be selected in the list box with the dynamic dimensions, so they appear in the chart. This while keeping the structure for the column names and the triggers you created with all the variables. I would also like to give the added possibility to deselect one or more of the 6 selected dimensions from the list box with the dynamic dimensions

I've tried to create extra variables for the 'Categories', and some of them work, but others don't. I tried to create a combined vAHClassDimCnd, which is supposed to take into account both the selection of a category and the dimensions linked to it, but I'm doing something wrong, I reckon. Any advice? See below for my restructuring of the dimension list, and for the variables I tried to add.

Dynamic Dimensions with Categories:

%AHDimOrd	%AHDim	%AHDimension	%AHSheet	%AHClass	%AHClassOrd	%Disable
1	ArtikelNummer	Artikel Nummer	Artikel	Basis	1
2	Artikel_N	Artikel_N (Nederlands)	Artikel	Basis	1
3	Artikel_F	Artikel_F (Frans)	Artikel	Basis	1
4	ArtikelEANCode	EAN Code	Artikel	Basis	1
5	ArtikelPLUCode	PLU Code	Artikel	Basis	1
6	ArtikelProductsuffix	Productsuffix	Artikel	Basis	1
7	ArtikelVerkoopLeverancier	Leverancier	Artikel	Aankoop	5
8	InkoopInfoRecordArtikelNummerLeverancier	Artikel Leverancier	Artikel	Aankoop	5
9	ArtikelVestigingStatusCode	Vestiging Status code	Artikel	Status	2
10	ArtikelVerkoopStatusCode	Verkoop Status code	Artikel	Status	2
11	ProductHïërarchie1	ProductHïërarchie1	Artikel	Indeling	3
12	ProductHiërarchie2	ProductHiërarchie2	Artikel	Indeling	3
13	ProductHiërarchie3	ProductHiërarchie3	Artikel	Indeling	3
14	ProductHiërarchie4	ProductHiërarchie4	Artikel	Indeling	3
15	ProductHiërarchie5	ProductHiërarchie5	Artikel	Indeling	3
16	ProductHiërarchie6	ProductHiërarchie6	Artikel	Indeling	3
17	ArtikelSoort	ArtikelSoort	Artikel	Indeling	3
18	ArtikelIntraStatCode	Intrastat	Artikel	Indeling	3
19	ArtikelGroep	ArtikelGroep	Artikel	Indeling	3
20	ArtikelLengte	Lengte	Artikel	Dimensies	4
21	ArtikelBreedte	Breedte	Artikel	Dimensies	4
22	ArtikelHoogte	Hoogte	Artikel	Dimensies	4
23	ArtikelGewicht	Gewicht	Artikel	Dimensies	4
24	ArtikelGewichtEenheid	Gewicht Eenheid	Artikel	Dimensies	4
25	ArtikelVerkoopVestigingLeverend	Vestiging	Artikel	Verkoop	7
26	ArtikelVerkoopPositieTypeGroep	PositieType	Artikel	Verkoop	7
27	ArtikelVerkoopVerkoopVanaf	Verkoop Vanaf	Artikel	Verkoop	7
28	InkoopInfoRecordOrganisatieInkoopGroep	InkoopGroep	Artikel	Aankoop	5
29	ArtikelBelastingClassificatie	Belasting Classificatie	Artikel	AankoopPrijs	6
30	ArtikelVerkoopAankoopPrijs	Aankoop prijs bruto	Artikel	AankoopPrijs	6
31	ArtikelVerkoopAankoopPrijsHoeveelheid	Prijs hoev.	Artikel	AankoopPrijs	6
32	ArtikelVerkoopAankoopPrijsEenheid	Prijs eenh	Artikel	AankoopPrijs	6
33	ArtikelVerkoopAankoopPrijsKortingZID4	Korting% ZID4	Artikel	AankoopPrijs	6
34	ArtikelVerkoopAankoopPrijsKortingZID7	Korting% ZID7	Artikel	AankoopPrijs	6
35	ArtikelVerkoopAankoopPrijsNettoStuks	Aankoop prijs netto / stuk	Artikel	AankoopPrijs	6
36	ArtikelVerkoopVerkoopPrijs	Verkoop Prijs	Artikel	VerkoopPrijs	8
37	ArtikelVerkoopVerkoopPrijsHoeveelheid	Prijs hoev.	Artikel	VerkoopPrijs	8
38	ArtikelVerkoopVerkoopPrijsEenheid	Prijs eenh	Artikel	VerkoopPrijs	8
39	ArtikelVerkoopConsumentPrijsIncl	Consument prijs incl	Artikel	ConsumentenPrijs	9
40	ArtikelVerkoopConsumentPrijsHoeveelheid	Prijs hoev.	Artikel	ConsumentenPrijs	9
41	ArtikelVerkoopConsumentPrijsEenheid	Prijs eenh	Artikel	ConsumentenPrijs	9
42	ArtikelVerkoopMinimumAfname	Min. afname	Artikel	Aankoop	5
43	ArtikelPalletAantal	Aantal per pallet	Artikel	Aankoop	5
44	ArtikelUOMHoeveelheidEenheid	UOM Eenheid	Artikel	Aankoop	5
45	ArtikelUOMLengte	UOM Lengte	Artikel	Dimensies	4
46	ArtikelUOMBreedte	UOM Breedte	Artikel	Dimensies	4
47	ArtikelUOMHoogte	UOM Hoogte	Artikel	Dimensies	4
48	ArtikelUOMEANCode	UOM EANcode	Artikel	Dimensies	4
49	ArtikelUOMHoeveelheidTeller	UOM Teller	Artikel	Dimensies	4
50	ArtikelUOMHoeveelheidNoemer	UOM Noemer	Artikel	Dimensies	4

Variables I tried to add:

vAHCClass	$(=SubField(vAHClassSelOutX,';',$1))	Ad-Hoc table class
vAHCClassCnd	Len(SubField(vAHClassSelOut,';',$1))>0	Ad-Hoc table class show condition
vAHCClassDimCnd	Len(SubField(vAHClassSelOut,';',$1))>0 and Len(SubField(vAHSelOut,';',$1))>0	Ad-Hoc table class+dimension show condition
vAHCClassTtl	SubField(vAHClassSelOut,';',$1)	Ad-Hoc table class column heading
vAHClass	SubField(vAHClassSelInit,';',%AHClassSelIn)	List box expression for class list box
vAHClassSelInC	=If(GetSelectedCount(%AHClassSelIn)>0,Concat(Chr(%AHClassSelIn+128)),'')	Unicode character list of class selections (unordered)
vAHClassSelInit	=Concat(%AHClass,';',%AHClassOrd)	Concatenated list of class user-friendly names
vAHClassSelInitX	=Concat(%AHClass,';',%AHClassOrd)	Concatenated list of class names
vAHClassSelInTgr	If(GetSelectedCount(%AHClassSelIn)=0,'',KeepChar(vAHClassSelOutC & PurgeChar(vAHClassSelInC,vAHClassSelOutC),vAHClassSelInC))	Trigger for %AHTrigger for %AHSelInSelIn
vAHClassSelOut	=If(GetSelectedCount(%AHClassSelIn)=0,'',Concat(SubField(vAHClassSelInit,';',Ord(If(Len(Mid(vAHClassSelOutC,ValueLoop(1,100),1))>0,Mid(vAHClassSelOutC,ValueLoop(1,100),1)))-128),';',ValueLoop(1,100)))	Concatenated list of selected class user-friendly names
vAHClassSelOutC		Unicode character list of class selections (selection ordered)
vAHClassSelOutX	=If(GetSelectedCount(%AHClassSelIn)=0,'',Concat(SubField(vAHClassSelInitX,';',Ord(If(Len(Mid(vAHClassSelOutC,ValueLoop(1,100),1))>0,Mid(vAHClassSelOutC,ValueLoop(1,100),1)))-128),';',ValueLoop(1,100)))	Concatenated list of selected class names

larry_w_ · ‎2019-04-29

This is similar to a problem I encountered with the Expressions list box.

I wanted to be able to have the user select a category of an expression and see only the expressions in that category.

The problem is with the way the list box functions in QlikView. The underlying dimension for the "dimension" and "expression" list boxes is simply a sequential integer. An expression in the list box uses the sequential integer to pick out a field from a delimited string constructed in a variable.

It's not a problem to change the delimited string such that it changes whenever you select a "Category", the problem is how the list box behaves:

Say you have 2 categories, "A" and "B". Category "A" has expressions "One" and "Two" while category "B" has expressions "Three" and "Four".

When category "A" is selected, the delimited string is "One;Two", when category B is selected the string is "Three;Four".

If you select category "A" then the list box shows the two entries. If expression "One" is selected, all is fine so far.

Then you switch category to "B" and two items appear in the list box (Three and Four). The moment you select one of these, the previous selection of "One" is cleared. QlikView sees that we're only selecting one of the displayed values in the list box, so it automatically clears the previous selections.
It's a big limitation not to be able to select from more than one category so this is not acceptable.

So, what I ended up doing is changing the sort order for the expressions (like in the demo app). You can choose a category and it will sort the available expressions by that category.
I think it may be possible to implement the categories, but it could get really complex and require multiple list boxes. I'd try to get your users to accept the sort order option.

I'll think about this further.

buzzario · ‎2019-04-29

Thanks for looking into this, Larry. I will discuss this with my lead and see if our users will be able to work comfortably with the changing sort orders of the list boxes. I will let you know as well if we find another solution!

buzzario · ‎2019-05-09

Hi Larry,

I did find some kind of workaround for the categorization. It's not entirely as I intended but at least it's something that works to present the dynamic dimensions a bit better visually. What I did is I added only these two variables concerning classification, and deleted all the other variables I made concerning classification.

vAHDimH	SubField(vAHSelInitH,';',%AHSelIn)	List box expression for dimension list box
vAHSelInitH	=Concat((%AHClass & '-' & %AHDimension),';',%AHDimOrd)	Concatenated list of dimension user-friendly names with hiërarchy

This allows me to present the list box with hierarchy as seen in screenshot below, while everything you created keeps working as intended. For now it's the best way I can find to present dynamic dimensions if you have a lot of them.

DDHiërarchy.PNG

larry_w_ · ‎2019-05-09

That looks like a good idea!

olgaLucia · ‎2023-04-20

Hola Larry, Me ha encantado tu aplicación. He descargado los archivos y ahora quiero replicar tu modelo en QLikSense. He encontrado que debo realizar varios cambios, sobre todo en el llamado de las variables. Me corriges si no es asi... Pero en sí mi gran duda es como crear las variables que guardan una lista de caracteres unicode, pues en su definición está en blanco y no he podido entender como se llena esa lista en las variables vAHExpOutC y vAHSelOutC.

Muchas gracias por tu ayuda.