“Code is read more often than it is written” – common programming adage As someone who often has to look at/modify QlikView script written by others, I have come to appreciate the value of readable script. Rather than taking an “if it works, it works” attitude, I always try to write my script with readability and consistency in mind. Somewhere down the line someone else (or worse, you) might have to decipher what a specific script is doing in order to modify or extend it. Today I want to ask your opinion on the readability of a specific part of the QlikView script, indentation of aliases within LOAD statements. To this end, I’ve taken a piece of script from the and formatted the aliases in three different ways. (note: I don’t agree with the absence of white space in functions and between operators, but have left that as is) Have a look at the three options, and then please answer the two multiple choice questions below. Option 1 Option 2 Option 3 You might wonder what the difference between option 2 and 3 is, but I’ll let you discover that for yourself.
Hello Experts, I forgot my Password of Hidden Script So can any one tell me how i can acess Hidden Script. Is their any location in QlikView from where we can read our Password. Is there any reason that this syntax shouldn't work in Qlikview load script?? I've tried both ways where I convert variable string to number and evaluate against the number variable directly and this.
If you don’t see it, just pick whatever you found most readable. Loading I’d be very interested in hearing your motivation for picking one option over the other, especially if you answered Something else to the second question. If you’d like to elaborate, or have any other thoughts around this subject, I welcome you to share them in the comments section below. About The Author Barry Harmsen Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book.
You can follow me on Twitter at. Yeah, I am aware of the comma at the front of the line thing. Personally, I don’t really find it aesthetically pleasing (but that shouldn’t be a reason in itself), don’t really have a problem with missing comma’s and find the overhead of moving all the comma’s too much hassle (the code generated by a table wizard puts them at the end of the line). To me, a comma means the end of a ‘statement’, so it should be on the same line as that statement.
Also, it just shifts the problem from the last column to the first column, although it’s more likely that you’ll be adding extra fields at the end instead of extra fields at the start. Most of this probably sounds arbitrary, my opinion is you should come up with some sort of convention for yourself and stick with it. And it works best if this convention is something that’s commonly used, as mixing two different conventions (for example, when you inherit work from another developer) is probably worse than no convention. I really like option 2 (3rd one looks confusing to me) and I try to do this formatting whenever I can. However it is easy to get this out of control in QV and it does not help that QV editor does not have any beautifier / formatter which are very common in SQL world. So it is normally a mix of option 1 and 2 – but i would prefer 2. For the first column, I align it with the comma in front of the second column: LOAD c1,c2; not: LOAD c1,c2; I also like to align parentheses and type a closing one before i start writing the expressions inside them: Also like to align statements and put; on a separate line like that: LOAD c1,c2.
Hi Barry, Being a meticulous person by nature I think about that as well and find it hard not to adjust inherited scripts. I am not a fan of having so much space between fields and aliases as it takes too much time to move your eyes from one point to another on the same line. The same applies for screens in various software packages (ERP and bespoke) where 90% uses your options 2/3. For example it is very easy to mix up a field and alias in lines 2 and 3 as they are too much separated.
It is also hard to align it when there are long functions, etc. In complex function and mathematical expressions I like to put a blank space between parameters/operator to increase readability. I use a mix like in the following excerpt: load upper(@5) as AuthenticatedUser, @1 as AuditserverStarted, @2 as ActionTimeStamp, SomeField, date(@2) &’’& time(@2, ‘hh’) &’’& time(@2, ‘mm’) as DateHourKey, Qty.
Price as Amount, if(index(@6, ‘ ’) 0, subfield(@6, ‘ ’, 1), ‘Selection’) as Action, textbetween(mid(filename, 1, index(filename, ‘.’) – 1) &’’, ‘’, ‘’) as QVSClusterNodeA Cheers. I want the fields on their own lines so I always put the LOAD statement on a line by itself by hitting enter before that first field.
Then I highlight the entire field list and hit Shift-Tab 5 times, followed by a single tab. That gets all the fields lined up and indented to the first tab stop. And while the fields are still highlighted I right-click and comment them all out. I then just uncomment the ones I want to use (at least during initial development). It’s a little bit of a pain to do this every time but it’s second nature for me now. Too bad their isn’t a way to customize the layout the wizards use. I agree that option 3 is neater.
I personally prefer to group related variables together (if order is not important), add new lines between expressions, and wrap my named variables in brackets. Here’s an example: LOAD Autonumber(@1) as %some ID, Autonumber(@2) as %someother ID, @3 as Something 1, @4 as Something 2, Upper(@5) as Something Else, // Format date as following “MM-DD-YYhhmm”: Date(@5, ‘MM-DD-YY’) & ‘’ & Time(@6, ‘hh’) & ‘’ & Time(@6, ‘mm’) as Some Date, Date(@4) as Some Other Date, // Format version information in a meaningful way, i.e. “Product Version #”: Pick( WildMatch(variable, ‘abc1’, ‘abc2’, ‘abc3’), ‘ABC version 1’, ‘ABC Version 2’, ‘ABC Version 3’ ) as Version. I’m in favour of aligning the aliases (option 2) and using leading commas.
Both require a proper editor that can automatically align and that provides a column selection. Currently Notepad is the only half-capable editor out there with a Qlikview mode.
Option 3 I find very confusing because there’s nothing on the left to correspond to the unaliased fieldnames on the right, but I automatically look for it and get lost. In any case it seems unnecessary to mix aliased and unaliased fields like this (and certainly not positional and named fields) I like to use preceding loads to assign meaningful names to everything before doing any logic. It can be a good way to separate blocks of logic as well. Take your Qlik skills to the next level at the. 2 tracks, Qlik Sense/QlikView and Qlik APIs.
'This has been a fantastic experience and the learning opportunities were enormous. Of all the training and conferences I attended for QlikView this has been above and beyond the best one to date.' - San Francisco Summit attendee 'When you finish the developer course you think you know it all, but when you come to this Summit you realize that you know nothing. It was much more than I expected.'
- London Summit attendee '“Great presentations, very energizing and inspiring. Lots of tips and tricks, ideas and experience exchange.' - Barcelona Summit attendee.
As you will know, if you have ever used it, QlikView makes it very quick to pull in data and present it in an interactive way. The downside is that apps created in QlikView always have to look a certain way. With a small amount of extra effort this does not have to be the case.
![Qlikview Qlikview](http://www.qlikfix.com/wp-content/uploads/2014/07/Subresult.png)
There are a number of UI defaults in QlikView that mean that you don’t have to worry about things, such as how to navigate between different sheets or where to put the caption on your chart. This is a great time saver, but it can mean that QlikView apps can end up not having the wow factor that the data being presented deserves.
Recently I have been working on a project for a company called Schools’ Intelligence , where the look and feel of the application was critical to the success of the project and ease of use had to be right to the fore. In order to ensure we ticked all the right boxes for the design we had to pull a few UI tricks out of the bag, and I would like to share these with you.
To see the finished app in action please at the foot of this post. Tabs, They Are Optional Along the top of many QlikView apps are the familiar tabs, directing the user to the right place.
Personally I like tabs, particularly now there is the drop down menu in the Full Browser version. Sometimes, however, a bit of extra flair can be added by integrating the navigation into the body of the app. Suppressing the tabs is a simple matter of going into the Document Properties (under the Settings menu), selecting the General tab and ticking Hide Tabrow: Turning off tabs doesn’t mean you don’t have to lose the ability to have sheets (though you can build everything on one sheet and use show and hide if you wish). Sheets can be created in the same way as when you have Tabs and can be navigated between by turning on the Sheets menu bar: Alternatively, put the tabs back on for development and just remember to turn them off before publishing. For our app we wanted a row of sheet navigation buttons in the app that changed colour to show where in the application you were, like this: Each button is simply a text box with legends on. My blog post on talks about how text boxes make the best buttons. Each text box has an action on it to activate the appropriate sheet: Note how the Sheet has been renamed (on the General tab of the Sheet Properties) to make it easier to work with.
The text boxes are created as linked objects across all sheets and the colouring of the buttons is done dynamically by looking at the sheet name. The code to set the colour dynamically reads like this: if(GetActiveSheetId = 'Document SHAttain', $(vTabOn), $(vTabOff)) The colours for the buttons are in variables, and there is similar code for changing the text colour.
This way positive feedback is given to the user that their button click has been acknowledged and there is always a reminder of where they are in the app. One of the big UI advantages of having custom sheet navigation buttons in your app is that you can then re-use the same style and colour of buttons elsewhere, bringing consistency to the user interface. Here we have buttons for switching dimensions and turning on targets that look the same as the navigation buttons: As with anything in user interface design, consistency is king. Hide The Unnecessary One of the key things with successful user interface design is to leave out as much as possible, so what is left is clean yet easy to understand.
Functions that are necessarily more complex can be hidden until required. The current selections box and the listboxes for making selections are hidden away until needed, with simple text objects showing the current state of things: The forward, back and clear buttons are present right in the UI (these are things I tend to leave out as they are on the Ajax menu, but they were requested here) as are basic statistics around what selections you have on and the impact of that on the number of records that are in play. The three items of text all have show / hide variables being set by them, and these are used to show the current selections box, a set of select criteria and a list of pupils respectively. Each item that can be shown by clicking on one of the links can be closed again by clicking on the same button, or on a close button tied to the screen that pops up: This makes it easy for the user to find what they need when they need it, but also to put it back out of the way when they are done. Another good way of hiding the unnecessary is to use silent legends. We use this on this display of pupil counts on the dashboard: Dashboards require nice big easy to read numbers. As well as the total number of pupils in the current selection this also shows the gender split.
Simple graphics and use of colours make it obvious which figures relate to which gender – without the need for a text legend. Another way clutter can be removed is by making items transparent or utilising a base colour.
In this example we are using the chart caption (another option is to turn this off) but making it the same colour as the chart background. By still having a caption, even one that is effectively hidden, we still have the placings for the fast change and XL buttons: As with everything in this post, by not just accepting defaults things can be made to look nicer and perform better. Handle Single Rows Differently A trap that I often see people fall in to with their QlikView apps is not providing anything different when you are looking at a single row of data. A number of bar charts that only have one bar on them are really not conveying much information. Drill Down groups in QlikView are a native way of dealing with this – but I find they can be confusing to users and it doesn’t offer a truly custom way of viewing a single row. In this application the single row of data we deal with differently is a single pupil record. When the selections are made that uniquely identify a pupil the Pupil tab changes to show a more detailed view.
Firstly, we can show the pupil name prominently, as this screen could be used to sit with a parent and talk about performance: We also use more of the object that I think is most under-used and under-appreciated in QlikView, the text box. Giving some of the headline information for this pupil: The selected pupils performance in the latest term vs. The previous term are shown in a radar chart, and a trellis chart shows their performance over time in each of the subjects – the sorting on the trellis means that the Core subjects are shown first without scrolling. By using Set Analysis the grey line on the trellis plots the performance of the other pupils in the year – providing a comparison. There is also a table of data, again specific to this pupil. The overall look and feel of this sheet is like this: The other subtle change when we move from many records to having a single record is our selections text changes to show the pupil name, even when we are on other tabs: As I’ve mentioned in previous blog posts on: Everything Matters. If you want to create a really good app attention to the smallest detail is important.
Make sure as well that you keep things simple, text boxes can often convey information better than other objects, as I cover in. The Finished Result Those were just some of the ways we went about producing a polished and pleasing to use application.
The techniques applied, whilst they look really effective, don’t actually require that much extra effort. In fact, some of the design features end up saving development time – such as having consistent linked objects across all sheets. The application has been produced by (with QlikView assistance from ourselves) to enable UK based Schools to be able to view and interpret the data they already have to collect in order to report back to Ofsted and other bodies. The Schools Intelligence system will allow schools to really get a view of how pupils are performing and what impact investments they are making in learning are having. From this more informed decisions can be made about the provision of learning, and that can only have a positive benefit on the children being taught. If you would like to find out more about the Schools’ Intelligence offering you may like to see this video featuring the app we built with them or visit their web site at.
![Qlikview Crack Hidden Script Programs Qlikview Crack Hidden Script Programs](/uploads/1/2/5/5/125514441/615597114.jpg)
Hi Kiran – both dimension and expression can be read from variables on a chart, so you can add buttons to allow users to visualise any measure over any dimension. The Attainment/Progress/Pupils button does this for the expression in our app. In the simplest implementation of this set two variables to defaults in the load script (e.g. Let vDim = ‘Customer’; let vExp = ‘sum(Sales)’;) and then set the calculated dimension to $(vDim) and the expression to $(vExp). Then set up buttons to update the variables to the users values. You can also use list boxes on top of data islands for a more dynamic way of achieving this, by loading dimensions and expressions into fields in the data model.
The possibilities are many! Hi Steve, Good looking dashboard. I do have two questions: 1. Why do some of the headline information text boxes have green backgrounds?
That makes them just about the most prominent things on the dashboard. But the information displayed there does not really seem all that important. And I find the green background text boxes are harder to read as well. Why a radar chart? That kind of chart takes up quite a bit of space, but I don’t see the advantage over using for example a bar chart for the kind of information displayed in this case. Regards, Gysbert. Hi Paul – thanks for your comments.
Design is usually arrived at by a number of factors. Most of my implementations are on tight deadlines and budgets – so things that are quite generic and not fussy can be the order of the day. It’s nice to have a project like this one, where the app will be provided to many schools and the look and feel is key – it gives more scope to spend the time to get things just right. When marketing departments wade in and start being dictatorial about colours and fonts things often don’t go well – what works for a brochure web site doesn’t necessarily translate well to analytics. As the article states though, don’t leave things to chance or leave design as an afterthought.
Even when time is tight getting simple things right (like alignment and colour) is essential. Hi steve, I have gone through one of your articles on Nprinting. Recently we are implementing a div integration of Qlikview objects into a website of the client, I know that a sheet can be exported into various formats like excel,pdf, csv etc but is it possible in a web page where a Qlikview object is integrated and when we right click or some other on that object can we export the data of that chart into different formats. Kindly suggest if there are any tools in view of div integration.
Hi Boris, Thanks for your comment, glad you like the UI. I would not like to say how long the UI took to build, but it certainly took more time than on some other projects delivering as much data.
As it is a dashboard that is being taken out and pitched as an OEM offering it had to be top notch. Simple things like alignment of buttons added a little to the effort, and overlapping object were sometimes a pain – but worth it. Generally though I am a big advocate of making your QlikView dashboard look like a QlikView dashboard, as going with the natural UI does make things simpler and quicker. The Supporting Data link just fires the user off to another sheet with a number of straight tables showing row level information.
There is a vertical scroll on this tab, as each table is shown one above another. It’s not likely to be used by many users (in fact I think the link has a show condition on it) but is useful during development. I inherited the data model and it was very much based around a link table.
This made some things a bit problematic. After taking the project on I concatenated more into the main fact table (as is generally my preference) but there is still a link table for some entities.