topic Exporting null-values to Excel are not really empty in QlikView

Exporting null-values to Excel are not really empty

Mon, 26 Mar 2012 13:13:44 GMT

Hey,

I got a problem when exporting Data from QV to MS-Excel:

My UI contains a table-chart with 3 columns: contract-number, account-number (may be null) and a sum (see attached qvw-file).

When I export the chart to Excel the null-values in the account-number column seem to be empty but (as I think) are not!

Reason:

On creating a Pivot-chart in excel (see sheet "Pivot" in the attachment's Excel-file) the number of of accountnumbers says 24 (instead of 16 not empty fields).

In order to get the correct result, i have to manually delete the empty cells again (del-button) and refresh the pivot chart.

My questions:

* Which value is exported (technically) to Excel when the cell is null in QV?

* What will I have to change in QV in order to be able to create pivot-charts without any manual steps?

thank you for your help,

Gazman

Exporting null-values to Excel are not really empty

m_woolf — Mon, 26 Mar 2012 13:25:55 GMT

I couldn't figure out what is acutally in the cells, Excel says:

The cell is not blank.

It is not a number.

It's length is zero.

I also tried the Code function to find an ascii value and got an error.

Why not make the pivottable in Qlikview?

My other thought is to write an Excel macro that clears the contents of those cells.

Exporting null-values to Excel are not really empty

Mon, 26 Mar 2012 13:32:30 GMT

Thank you for the fast reply!

I suggested the user (who brought up the "bug") that i would create a pivot-chart in QV but he needs the data for other operations (in excel) as well ;(

The macro-solution is also not acceptable for us ;(

Exporting null-values to Excel are not really empty

m_woolf — Mon, 26 Mar 2012 13:39:02 GMT

If you create a macro in QlikView that copies the full table to the clipboard and then pastes in Excel, the cells will be empty.

Exporting null-values to Excel are not really empty

m_woolf — Mon, 26 Mar 2012 13:45:23 GMT

Another thought:

If you set the Null symbol to -, Excel will see it as a label.

In the pivottable you can use count numbers.

Exporting null-values to Excel are not really empty

m_woolf — Mon, 26 Mar 2012 13:47:40 GMT

Count numbers works in the Excel pivottable even if there is no null symbol.

Exporting null-values to Excel are not really empty

Mon, 26 Mar 2012 13:49:40 GMT

Hmm, that looks promising! I'll suggest this to the enduser and hope he'll accept it!

Exporting null-values to Excel are not really empty

Mon, 26 Mar 2012 14:10:21 GMT

He is not very happy but he accepts the solution

If anyone knows another way to export "null"s i'd be happy anyways!