Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
did you try using the REPLACE Function
REPLACE(Fieldname,'#N/A','0') as FieldName
did you try using the REPLACE Function
REPLACE(Fieldname,'#N/A','0') as FieldName
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....
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
;
Use this it will work
Load
If(IsNull(column1) or column1='',0,column1) as column1
FROM
Book1.xlsx
..........
..........
..........
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