Surprise, loading data into Qlik is not always easy. But we already know you weren’t fooled when the salesperson confidently declared, “Any trained monkey can load your data into Qlik”. As data models get more complex I find myself joining, keeping, concatenating, mapping, resident loading and dropping tables. The Qlik script logic and syntax involved is not necessarily what I would label easy. Whether we use QlikView or Qlik Sense, these are some common scripting errors and how to resolve them. Also, please remember that the Qlik Community has always been an excellent place to get help for QlikView script errors you come across.
1. Comma Management
Symptom: All I did was comment some fields.
Likely Cause: Forgot to delete comma from what is now the last field.
This is an error we all make. The key here is to learn to recognize it quickly from the generated error messages that don’t really explain the cause. Just like in English grammar, we need commas between field names in a list and obviously we will remove the comma after the last field. Learn the general syntax rules regarding use of colons, semicolons, quotes and commas.
2. Missing QVD Directory
Symptom: I get a non-descript error or “Failed to open file in write mode…”
Likely Cause: Reference to a file location that does not exist.
This QlikView script error usually occurs when we are storing a QVD to a folder location that does not yet exist. It seems to me that in older versions of QlikView, the resulting error message was pretty much blank. The new error is much better. Make a mental note to always create the QVD folder in the file system before running this command in the QlikView script.
3. Forgetting to Drop Extra Tables
Symptom: Why do I have synthetic keys all of a sudden?
Likely Cause: Not Dropping Tables on a resident load.
There are many times when, for whatever reason, we must load a table again from a resident load. When the field mix in the new table is slightly different because we added a field or we renamed a field, this has the potential to create a large synthetic key. So if we resident load a table and all of a sudden we have a synthetic key, we likely forgot to drop the first version of the table.
4. Neglecting to account for table concatenation
Symptom: I lost the new version of the table.
Likely Cause: Automatic Concatenation on resident load.
This is related to the prior situation. If we do a resident load to create a new version of the table and the table viewer completely loses the new and old versions of the table, it could send us into a panic. “What happened to my table?” What we might not remember is that when Qlik loads a new table that shares the exact same fields as a prior loaded table, the new table will be concatenated to the old table. And then because we subsequently drop the first table, there is no table left with our data. Adding a noconcatenate quickly solves the problem by telling Qlik to keep the tables separate.
5. Cross joined tables
Symptom: My Qlik reload locked up.
Likely Cause: Joins with no keys.
When I first encountered a “stuck” script execution window, my heart sunk. “Gee, QlikView sure is buggy.” Of course, this was not a case of Qlik being buggy. Qlik was actually working very hard to create associations between a few tables. The problem was that I had joined one table to another with a typo between what I intended to be the key fields. When using the join keyword be mindful of the fields that Qlik will identify as keys and double-check to be sure they are named exactly the same. If the fields do not match, Qlik will associate every row of one table to every row of the other. This joining process can slow down the script at best and take your server down at worst. If our script appears to stop running for more than a few minutes this is the likely cause. Either way, this was not the resulting table we were looking for. Table 1: 100,000 rows Table 2: 5,000 rows Joined table: 500,000,000 (That’s 5 hundred million. Ugh.)
6. Repeating Facts after Join
Symptom: My joined table has more records than it started with.
Likely Cause: Left joining with repeating keys
I find this to be a data error that is very common and overlooked by many developers. Picture a transaction table where we simply want to left join the Customer Name into. This seemingly harmless action unfortunately might inflate the actual transaction expressions in our dashboard. This is because if a Customer ID from the right table has any repeats, the resulting table will add those repeats to the transaction table. If the right table has no repeated keys, then we are fine. But when doing such an action, use the lastvalue function with a group by or similar function to ensure you are only taking one ID. Whatever you do, please do not rely on a distinct qualifier with the left join as we will accidentally remove some records from the left transaction table as well because the resulting table will also take on the distinctiveness. Checking for row counts in the table viewer before and after our joins is a good way to check the row counts.
Qlik Freak came up with another list of gripes that are also extremely funny so check that out. Surely, we are just scratching the surface of the QlikView script errors that can occur. But if we understand these basic pitfalls, we are in a state to better deal with any script or data error that we might run across. What Qlik script errors have plagued your coding?