Exporting fro Qlik sense to excel (MISSING VALUES in excel but not in Qlik sense)

[famoka]

Has someone encountered (and been able to fix) the following issue?

We have GTIN-codes (e.g. 6415131402342) in a vizlib table. Original data source is azure databricks. When exported from QS to excel we get a small portion of the rows so that the GTIN-code column is somehow not matching a seemingly completely identical value (while doing vlookup etc. in excel).

There is no identifiable common factor explaining why some of the rows are not being interpreted correctly:

An example:

We have GTIN

6410402149469

We compare to same number from another sheet, we are able to match it. .--> OK

Then we might have

6410402121114

This exact same number is present when doign the comparison but results in #N/A

• there is nothing extra in the cells when inspecting
• they are same data type in excel
  
  --> Somehow yet with Trimming or rounding in excel side we can match them???
  
  We are also able to match right away correctly, but only if we export in .csv format from QS.
  
  So far we've tried to trim, round, floor, try different data types at databricks and qliksense side but the problem seems to happen at the point of extracting from the table. 

[Scotchy]

The issue you're describing with GTIN codes not matching in Excel after being exported from Qlik Sense is odd. Here are possible factors that could be causing this problem and potential solutions:

1. Formatting During Export:

   • Sometimes, data may be formatted differently during the export process. Ensure that the GTIN codes are being exported as text to preserve leading zeros and avoid any scientific notation conversion that Excel might apply to long numbers.

2. Data Type Issues:

   • In Qlik Sense, make sure the field is treated as text, not as a number. You can enforce this by using the Text() function in your load script, like this: Text(GTIN) as GTIN.

3. Excel's Automatic Conversion:

   • Excel might be automatically converting the GTIN codes into numbers, which can cause issues with large integers or with leading zeros.
   • To prevent this, you might try pre-formatting the Excel cells as text before importing or paste special as text.

4. Hidden Characters:

   • There might be non-printable characters in the GTIN codes that are not visible. Try using the PurgeChar() function in Qlik to remove any potential non-printable characters before exporting.

5. Precision and Large Numbers:

   • Excel has a 15-digit precision limit for numbers. If your GTIN codes are being interpreted as numbers and are longer than 15 digits, Excel may alter the last digits. Exporting as text should resolve this.

6. CSV Export:

   • Since you mentioned that exporting as CSV works fine, compare the CSV content with the Excel export using a text editor to see if there's any difference in the GTIN codes between the two files.

7. Regional Settings:

   • Check regional settings in both Qlik Sense and Excel. Different decimal and thousand separators might cause issues.

8. Excel Import Method:

   • If you're importing the data into Excel using Data -> Get & Transform (or the older Data -> From Text), try using different import settings. Specifically, ensure that the column is being imported as Text.

9. File Format:

   • If the GTIN codes are exported in scientific notation when using Excel format, try exporting to XLSX instead of the older XLS format, as XLSX handles large numbers and precision better.

10. Macro or Script:

    • As a last resort, you might consider writing a VBA macro or a script in Excel that processes the data after import to ensure it matches the expected format.

11. Vizlib Specifics:

    • Since you're using a Vizlib table, check if there are any specific settings within the Vizlib table properties that could be impacting the export format.

Remember, the goal is to ensure the GTINs are treated and remain as text strings throughout the entire process, from Qlik Sense load script, to sheet visualization, to export, and finally to Excel import. If none of the above solutions work, I recommend reaching out to Qlik Support for further assistance, as this might be a more intricate issue with the software itself.