topic Re: Number format export to excel issue in QlikView

Number format export to excel issue

cthomas2 — Wed, 25 Nov 2020 16:16:04 GMT

I have a very odd issue that I cannot figure out the root cause as to the issue. I have 2 separate QV dashboards that pull in the same field, but from 2 different QVD's. In the Number area of the Document Properties, I set the exact same settings and do NOT override the settings at the object level.

I set the Number settings to "Fixed To" and Precision = 3, and supply my own mask of #,##0.000;(#,##0.000).

In one dashboard, when I export to excel, it works 100% as expected. If I have a number say 0.0123... the format forces 0.012, and when I click on the cell to see the value, it shows 0.012.

However, in the problem dashboard, when I follow this exact same scenario, that value will be displayed as 0.012 in the Excel export, but if I click on the cell to see the value, it shows me 0.0123. I cannot for the life of me figure out why it works on one dashboard and not the other.

Both objects in the individual dashboards are straight table objects. And, in both dashboards there are no override settings applied on the table objects in the Number tabs.

Hopefully I've described the scenario very clearly, because this actually is a problem on ALL fields in one dashboard and not the other where this mask is applied. In one dashboard it correctly exports all fields as a display value of 0.012 and a cell value of .012, and in the other dashboard it will have a display of 0.012 but a cell value of 0.0123.

The reason this is an issue is because if someone were to generate their own numbers off the excel export, it will not match what they see in QlikView.

Please help with any ideas, I hope this is something very simple I'm missing!!!

Re: Number format export to excel issue

m_woolf — Fri, 11 Jan 2019 19:29:17 GMT

You said the two dashboards pull the same field but from different QVDs. Is the data different in the 2 QVDs?

Re: Number format export to excel issue

cthomas2 — Fri, 11 Jan 2019 19:57:52 GMT

It occurs on ANY number which has more precision than 3 in one dashboard, but on any field where we apply the mask in the other dashboard, all fields with more precision than 3 from the QVD are being corrected.

I described the problem using one field, but this is occurring on ANY field where precision is higher than 3 naturally from the QVD in one dashboard, yet the number mask correctly adjusts the output on the other dashboard for higher precision number fields.

I mainly pointed out that it is 2 different QVD's in case someone thinks there is something I can check for on the QVD itself. In the load script, both fields are pulled in the exact same way (which is unmodified).

Re: Number format export to excel issue

m_woolf — Fri, 11 Jan 2019 20:18:45 GMT

Any overrides for the fields in Settings|Document Properties|Number?

Re: Number format export to excel issue

jonathandienst — Mon, 14 Jan 2019 07:04:49 GMT

Its possible that the meta-data in the QVD defines the fields differently. You can check that by examining the XML headers for the two QVDs. Or you can use a QVD viewer tool that displays the meta-data in a more readable form.

Re: Number format export to excel issue

cthomas2 — Mon, 14 Jan 2019 12:46:44 GMT

Hold up on this one, we may have not been lining up the data correctly between the two outputs (which makes me feel a lot better that it would be us and not the tool :))

We found examples now in both outputs, sorry for the mixup. It is a LOT of data 🙂

That said, anyone know how to get the cell value to line up with the display value? 🙂

Re: Number format export to excel issue

cthomas2 — Tue, 15 Jan 2019 19:49:20 GMT

Ok for anyone else that comes across this, we figured out it is because in one case, the data is exported from a chart object and in the other, it is exported from a table box. It is only an issue when exported from a table box.

We will either convert the table box to a chart, or round the data as it comes in through the load script.