topic AW:Re: Problem while exporting to EXCEL in QlikView

Problem while exporting to EXCEL

Tue, 12 Jan 2010 17:48:23 GMT

Hello !

I have this tiny attached QVW file with only a SINGLE_FIELD.

When I view this SINGLE_FIELD inside QV itself, it works fine.

However, when I export this field to EXCEL, I have an error on the field value (it's altered).

Guess it's because I'm using the NUM#() function to load the field. The idea is to "force" the field to be loaded as numeric, ALWAYS.

Hope someone else can help me out with this problem !

Thanks !

Problem while exporting to EXCEL

Wed, 20 Jan 2010 07:30:06 GMT

Hi,

I have tried your sample application and tried to export it to Excel:

No error.
By the way, the num# cannot be the problem during export because you are using the num# in the load-scripts which is finished after loading 🙂

Maybe you should give it a new try or post here more details on your "error" ...

Best regards

Stefan

AW:Re: Problem while exporting to EXCEL

Wed, 20 Jan 2010 08:54:49 GMT

Hi,

that a true error, not? The Value in QV is 7346076854.

I need the source file to test the thing. Thanks.

Have Fun with QV

Alex:)

AW:Re: Problem while exporting to EXCEL

Wed, 20 Jan 2010 19:19:49 GMT

Yes the original value is not negative as it is shown in Excel !

Attached original file.

AW:Re: Problem while exporting to EXCEL

Thu, 21 Jan 2010 00:24:05 GMT

Currently you are using

NUM#(SINGLE_FIELD) AS SINGLE_FIELD

Try using

NUM#(SINGLE_FIELD,'#') AS SINGLE_FIELD

Regards,

Nimish

AW:Re: Problem while exporting to EXCEL

Thu, 21 Jan 2010 11:04:03 GMT

Thank you so much ! It works.

But I'd like to understand what's going on...

AW:Re: Problem while exporting to EXCEL

Thu, 21 Jan 2010 11:25:53 GMT

The fact is that I load my data from CSV files and I'd like to detect invalid content during script load.

Say that I have a supposed numeric field on my CSV file. I dont trust that much that all of the records will contain a numeric content for this field.

So I have applied this syntax for this field load :

IF(SINGLE_FIELD = '','',IF(ISNUM(NUM#(SINGLE_FIELD,'#')),NUM#(SINGLE_FIELD,'#'),'INVALID NUMBER')) AS SINGLE_FIELD

Is that correct ? Is this the best syntax for this sort of error detection ?

I also do the same for Date fields , like this :

IF(SINGLE_FIELD = '','',IF(ISNUM(NUM#(SINGLE_FIELD)),NUM#(SINGLE_FIELD),'INVALID NUMBER')) AS SINGLE_FIELD

And for Time fields I apply this :

IF(TIME_FIELD = '','',IF(ISNUM(TIME#(TIME_FIELD,'hh:mm:ss')),TIME#(TIME_FIELD,'hh:mm:ss'),'INVALID TIME')) AS TIME_FIELD

The general idea is that the invalid content "bubles up" as a string on a List Box.

Thanks in advance for any help !

AW:Re: Problem while exporting to EXCEL

Thu, 21 Jan 2010 11:42:08 GMT

You may want to use the alt function

alt(Num#(SINGLE_FIELD,'#'),'Invalid Number')

You can have multiple checks in this. The last parameter is used for if everything else fails. The same can be done for date formats.

Nimish

AW:Re: Problem while exporting to EXCEL

Thu, 21 Jan 2010 12:15:29 GMT

Great ! Great ! Great !

Very aesthetic solution !

I knew about the ALT function but forgot to use it on this context !

Anyway, once more, thank you so much !