70 – Pick Match

One of the most resource intensive functions in a chart is the If statement. In a lot of scenarios, what you can do is leverage the combination of Pick and Match to replicate the functionality while increasing performance. Example: =If([Inventory Type]='Individual', Sum(Qty), If([Inventory Type] = 'Bulk', Sum(BulkQty)) Pick(Match([Inventory Type],'Individual','Bulk'), Sum(Qty), Sum(BulkQty)) If yo...

71 – Copy Paste

There’s nothing worse than waiting for a long reload to find out you made a typo. One thing I have made a habit of is copy and pasting script items: table names, field names, variables, etc… This is definitely a simple item, but it can save you a ton of time down the road. If […]

72 – Dual Set Analysis

By default you need to reference the display (read: string) value of a dual field in set analysis versus the numerical value. I personally think this is a weird behavior of Qlik, however it is what it is. The downside to this is that string operations are much slower than numerical ones. To overcome this […]

73 – Wildcard Load

One good thing to know is that you can use an asterisk * within a file name. Qlik will load all the files that fit the criteria. Say you have a directory named QVD, which has all of your QVDs prepped and ready for your data model. You can simply do the following to load […]

74 – SubField

If you are like me, you probably use SubField a lot. I had used it for years before I stumbled across one of its most powerful features in someone else’s code. If you do not provide the position parameter, it will expand a record for each value. I’ve worked with a few older databases that […]

75 – Arrays

Arrays and/or Lists are very common in programming languages. In Qlik scripting there are two ways to go about creating an array of sorts. The first way is storing a list in a variable: SET vCount = 'One','Two','Three'; for each i in vCount     Trace ------------ $(i); Next i The second way is by using a […]

76 – Remove Unused Fields

I can’t tell you how many times a client has said to leave a field in the data model just in case they want to use it later. Just don’t do it. Take it out now, if they want to use it later, then bring it in later. You can easily set up the app […]

77 – Keys

Here are a few things I strongly suggest in working with Qlik key fields. Make separate key fields* Prefix the field with ‘%’ Set HidePrefix variable to ‘%’ Use AutoNumber *Because of how the association engine works, you should never use a key field in an expression. It can return some very unexpected results. So […]

78 – AutoNumber

To follow up on my cardinality post, a very useful function is AutoNumber. AutoNumber will create an integer for each distinct value in your defined namespace, which is usually a field. The second parameter, allows you to define the namespace, which means you can use it across multiple fields while keeping data association integrity. The […]

79 – Cardinality

One of the key things to understand when trying to tune a Qlik app for performance is understanding the inner-workings. If you haven’t done so already, I very strongly suggest you read Henric’s blog Symbol Tables and Bit-Stuffed Pointers which goes into detail on how Qlik stores data. After grasping that, it starts to makes […]