Invalid Numbers when using Data Export

[AnnaRodriguez]

Is there anything different in formatting numbers in Qliksense vs Qlikview when exporting data to Excel? We noticed that some numbers in Qliksense are not identified as numbers when doing a vlookup after exporting data. I have chart in QS where ItemID (number) is used as dimension. When exported to Excel and validating data against Qlikview, the vlookup returns #N/A, even though Excel does recognize the Qliksense exported data as numbers. Column explanation to chart :

• A: Qliksense exported number
• B: Isnumber function on col A
• C: vLookup to Qlikview numbers (on a separate tab in the same Excel)
• 😧 vLookup using col A * 1 to Qlikview numbers
• E: Value of col A * 1
• F: vLookup of col E to Qlikview
• G: vLookup to Qlikview using numbervalue on col A

Only if i used vLookup with numbervalue, i was able to match numbers properly. This is confusing why some numbers in Qliksense are recognized as numbers while some not.

I noticed that when i click 'enter' on the cells in col A that are not matched, the value is recognized by the vLookup as number and i get a match. 

I am attaching the Excel file here. Anyone has experienced the similar issue?

[mpc]

Hi, 

QlikView Excel format depends of your QlikView version and numer formatting, if mixed, MS Excel will decide wich format to apply. More info here: https://support.qlik.com/articles/000005592

Kind regards

[AnnaRodriguez]

Hi, the issue is in Qliksense data export, not Qlikview. When the same chart is exported from Qlikview numbers are treated as numbers just fine in vLookup. When i export chart (as data download) in Qliksense, this is where i see the problem.

[NadiaB]

Hi @AnnaRodriguez 

By any chance could you confirm the following:

1. Are you using the same load script in both QV or QS

2. What data type is the field that is presenting the problem, this can be verified from the Data Model Viewer

3. When it comes to the object that you are exporting, does it have similar configurations (explicit conversions) than the object in QV?

It will be difficult to point out what the difference is without sample apps, the details about what to expect for Qlik Sense would be:

https://help.qlik.com/en-US/sense/May2024/Subsystems/Hub/Content/Sense_Hub/DataExport/export-data.ht...

As both are different products and different configurations that could be put in place, best option is to make sure the value is numerical or make an explicit conversion. 

Kind Regards. 

[AnnaRodriguez]

Answeres to the questions below:

• 1. Yes, logic is the same, backend data is the same. (load script is the same)
• 2. Column, ItemID is a number in QV and QS (as well as in backend table form which it is pulled).
• 3. Chart is the same in QV and QS, we use ItemID as dimension (no special formatting applied).

Let me remind you that I am not trying to review Qlikview data extract here. There seems to be some formatting issue in Qliksense that I need help with to resolve. I gave example of Qlikview, as this issue does not seem to be affecting Qlikview export.

It was our client who noticed this problem in first place. They had local Excel file with formulas (vlookup) embedded and for years they exported Qlikview data and all their vLookup formulas on ItemID worked w/o a problem. 

When we switched client to Qliksense, the same export of ItemIDs resulted in '#N/A' issues in their local Excel where vLookup on ItemID was used. The logic was working fine for some numbers and not for others. I think i provided sufficient information in attached Excel file with a portion of that data. I do not want to find differences between QS and QV, i need to find solution in Qliksense, or understand why Qliksense export results in mixed formatting.