Problem while exporting to EXCEL

[#1]

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 !

[#2]

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

[#3]

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:)

[#4]

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

Attached original file.

[#5 · Accepted solution]

Currently you are using

NUM#(SINGLE_FIELD) AS SINGLE_FIELD

Try using

NUM#(SINGLE_FIELD,'#') AS SINGLE_FIELD

Regards,

Nimish

[#6]

Thank you so much ! It works.

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

[#7]

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 !

[#8]

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

[#9]

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 !