Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

Tags (4)
1 Solution

Accepted Solutions
rustyfishbones
Honored Contributor II

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

did you try using the REPLACE Function

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

5 Replies
rustyfishbones
Honored Contributor II

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

did you try using the REPLACE Function

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

israrkhan
Valued Contributor II

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

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....

Not applicable

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

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_am
Contributor II

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

Use this it will work

Load

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

FROM

Book1.xlsx

..........

..........

..........

Not applicable

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

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 Smiley Happy Thanks! J

Community Browser