Exporting null-values to Excel are not really empty

[#1]

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

[m_woolf]

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.

[#3]

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 ;(

[m_woolf]

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.

[m_woolf]

Another thought:

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

In the pivottable you can use count numbers.

[m_woolf]

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

[#7]

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

[#8]

He is not very happy but he accepts the solution

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