topic Re: Excel File with Name Error in QlikView

Excel File with Name Error

williamlau — Mon, 25 Apr 2016 19:43:12 GMT

I have an Excel document that's read-only and it has almost 100K records. There's a particular column where on some rows, Name Errors exist. If you hover the mouse over the field, the popup says "unrecognized text' and in the cell, it displays "#NAME?". The underlying cell shows this as the value "=-Fail control ...". I think that having the equal sign as a prefix for the rest of the text is screwing everybody up.

When I bring this column into Qlikview, the field is blank for these suspect rows. I need to know if there's any data in that field to begin with.

Thanks in Advance.

Re: Excel File with Name Error

swuehl — Mon, 25 Apr 2016 21:03:06 GMT

Not sure what you are asking for.

If the cell in your excel file already shows an error when you looking at the cell value using Excel, you need to fix this in your excel file.

The data you are expecting to be there is probably a result of a cell expression, but this expression fails to evaluate.

So you need to check why. But that's an excel domain issue, so you are probably better off with asking your local office guru for help (maybe it's something simple like a missing referenced file).

Re: Excel File with Name Error

williamlau — Tue, 26 Apr 2016 13:20:11 GMT

The cell does show an error. The cell has the Nabisco corner on the top left.

This file is coming from a client and its read-only. We don't care so much about the content of the cell. We only care if there's something in that cell. Even if the cell shows "#NAME?", I want that.

Re: Excel File with Name Error

marcus_sommer — Wed, 27 Apr 2016 07:44:22 GMT

I think swuehl is right and you need to fix it within the excel, like:

if(iserror(YourExpression());"Error";YourExpression))

then the cell will be empty or contain NULL and have therefore no "real" value. That excel showed for such a cell an error like "#NAME?" is an additionally feature like a formatting, for example showing 27.04.2016 for today() but the cell-value is 42487.

- Marcus