Troubleshooting - Duplicate Records (Update)

This topic contains the following sections:

Duplicate Records - Overview

When you're working with Vizlib Writeback Table, duplicate records are something that you want to avoid. This article tries to explain why they happen and how to find a solution to your use case.

Data is Loaded Twice

It's easy to forget to comment out or modify the original data load when configuring the app with Vizlib Writeback Table or Input Form. This can happen when:

  • A load script is injected from the Setup Wizard. The Inject load script function is not a requirement, it's there for beginner Qlik developers to help them get started with Writeback quicker. The injected load script should be considered as a starting point and if needed should be modified to fit the need of the use case.

  • Data is loaded from an Excel file and stored in a QVD file to be used with Vizlib Writeback Table. Remember to comment out the load statement from the Excel file.

Figure 1: Data Load Editor with Duplicate Load Script Commented Out

In more advanced use cases a data blend is a good approach to apply. Audit columns store when a record is updated, this can be used to load the latest updated values either from the original data source or the user-generated edits using Vizlib Writeback Table. Add the data blend logic to your load script.

Synthetic Keys

Figure 2: Synthetic Key Example in Data Model Viewer

Qlik Sense creates synthetic keys when two or more data tables have two or more fields in common (Figure 2). These keys are anonymous fields that represent all occurring combinations of the composite key. This is something you want to avoid when working with Vizlib Writeback Table. Synthetic keys have their use in Qlik Sense but those are advanced use cases and you as a developer should know how those affect your data model in Qlik, and your Writeback setup.

In most cases, they can be easily fixed by creating a concatenated key and use that when writing back data. The Qualify script function can also be useful depending on your use case.

Measures and Calculated Dimensions

When a measure or a calculated dimension is used in the Writeback Table the data for that column is not always the same as the data in the data source. When Writeback Table tries to match the records to be updated it tries to find records with the values from the measure or calculated columns and if they do not exist it will insert a new record instead. To fix this:

  • Replace measures or calculated dimensions with calculated columns. The benefits of using a calculated column are excluded from matching and other columns can be referenced in the calculation. See Referencing Column/Field values in Expressions for more information.

  • Update the data model if needed and replace measures or calculated dimensions with regular dimensions.

  • If using a SQL Destination, check Match rows by Primary Key in SQL Destination Settings in the VMC (Figure 3).

    Note: The primary key column needs to be a regular dimension.

Figure 3: Match Rows by Primary Key

  • Check Exclude measures and calculated dimensions (Figure 4) in the property panel under Writeback Settings - Destinations.

Figure 4: Exclude Measures and Calculated Dimensions

Note: Is it important that the Writeback Table has at least 1 regular dimension that can act as the key to match the record to update. Exclude measures and calculated dimensions settings require version 3.5.0 or higher of Vizlib Writeback Table.

Large Numbers or Too Many Decimals

Floating-point values are, by definition, not exact values, and the stored value can vary on the last decimal digits. For example, the value 1.75 can be rounded upwards or downwards, but still represent the same decimal value.

1.7500000000000001

1.7500000000000000

1.7499999999999999

This can cause issues when updating values in a QVD file. If Vizlib Writeback Table can't match the value to update it will insert the value instead.

Read more about numeric values and floating-point precision in these Qlik knowledge base articles:

A good practice is to use custom Export number precision (Figure 5) and set the number of decimals to export to the required precision. Quantity values can suffice with 0 number of decimals and monetary values would suffice with 2 number of decimals.

Figure 5: Custom export number precision

Working with Floating Points - Workaround and Guides

Here are three guides that can help with floating-point-related issues:

Duplicates Caused by Decimals - Workaround

Note: Is it important that the Vizlib Writeback Table has at least 1 regular dimension that can act as the key to match the record to update. Exclude measures and calculated dimensions settings require version 3.6.0 or higher of Vizlib Writeback Table.

  • Convert numeric dimension columns to calculated dimensions by adding an equal sign before the field name and wrap it with square brackets (Figure 6).

Figure 6: Calculated Dimensions

  • Check Exclude measures and calculated dimensions (Figure 7) in the property panel under Writeback Settings - Destinations.

Figure 7: Exclude Measures and Calculated Dimensions

How to Fix Issues with Large Numbers Used as IDs

Fixing big numbers when they are used as id is fairly straightforward. What you want to do is change the id to be interpreted by the Qlik Engine as a text instead of a number, which removes the issue with floating-point precision. Append an arbitrary string to the id and it's sorted (Figure 8).

Figure 8: Append prefix to id to treat is a text field

Figure 9: Load the fixed file instead of the numeric id file

How to Fix the Issue with Too Many Decimals

The simplest way to fix the issue with too many decimals is to load the data source file into a new application and use the Vizlib Writeback Table (Overwrite operation) to create a fixed QVD file.

Figure 10: Excel file with too many decimals

  • Create a new empty app and load the source data with too many decimals with a star load.

Figure 11: Data load editor, load source QVD

  • Add a Writeback Table to the sheet. Setup Writeback Table with all the fields in the qvd. This is super quick if using the wizard. Click Set up to open up the Setup Wizard.

Figure 12: Create a Vizlib Writeback Table and open up Setup Wizard

  • Configure it so it writes to a new QVD. Uncheck Set to Update (Default) and change the operation to Overwrite. Click Continue.

Figure 13: Configure destination settings

  • Select all fields from the data model table Data, by checking the check box to the left of the table name.

Figure 14: Select all fields from the data model table Data

  • Click the highlighted plus icon to add all the fields in the Data table to the Writeback Table, and click Continue.

Figure 15: Add all columns to the Writeback Table

  • Skip Inject to load script, it's not needed since we only want to create a QVD with correct decimal precision. Click Save.

Figure 16: Skip Inject to load script and click Save

  • Click the Writeback data button to execute the writeback.

Figure 17: Writeback Data

  • Press Ok to confirm that the new fixed QVD should be created.

Figure 18: New File Confirmation Dialog

After the reload the data is fixed, replace the non-working data source with the fixed QVD, in the load script, and in the Writeback Table destination settings.

Figure 19: Update load script after QVD file is fixed