6 Common QlikView Script Errors

qlikview script pitfalls - a screenshot from the video game pitfall

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. QlikView Script Error - Missing Comma

QlikView Script - Highlighting the comma error

Forgot to remove comma from line 5 not to mention we still need the semicolon that is now commented.

QlikView Script - Highlighting the comma correction

The corrected code.

 

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. QlikView Script Error - Missing QVD Directory

File Directory - Added the missing QVD Folder

We simply added the folder to the directory so that Qlik would find the location when the script is executed.

 

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.

QlikView Script - Highlighting dropping table error

This is the original script. A common task, but aren’t we missing something?

QlikView Table Viewer- Synthetic Key error

Note that we now have an undesired synthetic key.

QlikView Script - Highlighting dropping table correction

The corrected script includes a drop statement to trash the original 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.

QlikView Script - Highlighting lost table issue

Because the field mix of the 2 tables is identical, the tables will be automatically concatenated, which is not what we want here.

QlikView Script - Highlighting lost table correction

The simple addition of noconcatenate ensures the tables will remain separate at which point our drop statement will only apply to the original data table.

 

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.)

QlikView Script Execution Window - Stuck Window

Qlik is hard-at-work joining every row to every row.

QlikView Script - Highlighting typo in key field

Notice that the fields I intended the join to occur over are not EXACTLY the same.

QlikView Table Viewer - Cross Join problem

In this case my resulting table has 40 million records (20,000 x 20,000)

QlikView Table Viewer - Cross Join correction

After I rename the field to be exactly like the field in the first table, I am back down to 20,000 records. Perfect.

 

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.

QlikView Script - Highlighting left join repeat records

This is a common left join. But there is trouble afoot.

QlikView Table Viewer - left join adding extra rows

We should only have 19 transactions. But because a Customer ID is repeated in the Customer table, I now have 26 lines.

QlikView Script - Highlighting repeat record fixes

Here are the corrections. We are ensuring that only 1 record will be joined to the transactions for each unique ID.

QlikView Table Viewer - Left join adding extra rows corrected

Now we are back to 19 records.

 

QlikView Screenshot - The Good Data against the Bad Data

Here is the good data and bad data side-by-side. Obviously, we do not want to inflate our primary metric.

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?

2 comments… add one
  • Reply Aaron Berman November 17, 2016, 11:58 am

    Hmmm, NOCONCATENATE. That was bugging me for awhile this morning. Thanks for the tip!

Leave a Comment

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons