Replace #N/A cells with 0 when importing from Excel

[#1]

Hello all, I searched first, but I could not find this question asked here before:

When I am importing data from Excel, certain cells contain the value #N/A instead of 0 (zero) which blocks other calculations in Qlikview.

Is there a way to replace the #N/A with the integer 0 via the script in QlikView?

Thanks a lot for any help!

Regards,

J

[rustyfishbones · Accepted solution]

did you try using the REPLACE Function

REPLACE(Fieldname,'#N/A','0') as FieldName

[israrkhan]

I Think a simple solution will work,

1) right click on cell in excel file and chose format cell, set format to number.

2) load the script with the below condition.

LOAD Field1,

     Field2,

     IF( Field3='' OR NOT ISNUM( Field3),0, Field3) AS  Field

FROM your sheet,...

NOT ISNUM(Filed3), will check weather Field3 has number data or string, string like #N/A, so it will be replaced with 0

Hope it helps....

[#4]

You can also use ApplyMap if you are validating more fields

MAP_VALID:

MAPPING LOAD * INLINE [

A, B

#NA, 0 ];

LOAD

ApplyMap('MAP_VALID',FIELD1,FIELD1) AS FIELD1,

ApplyMap('MAP_VALID',FIELD1,FIELD2) AS FIELD2,

.

.

FROM FILENAME.xls

;

[Yousef_Amarneh]

Use this it will work

Load

If(IsNull(column1) or column1='',0,column1) as column1

FROM

Book1.xlsx

..........

..........

..........

[#6]

Wow, each time I'm suprised with the amount of useful replies and help around here, so thanks a lot for that! I went with the first and easiest answer, which did what I was looking for. Other replies are useful as well, but too 'complex' for my need 🙂 Thanks! J