Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 |
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.
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!
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.
That looks like a good idea!
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.