topic Re: Handling Nulls and Blank fields in QlikView

Handling Nulls and Blank fields

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am loading data form excel. My field as blanks. So when I do if(isnull(trim((Field)),'Y',"N"), I get N for blanks. I checked on the length, it is returning 4 for blanks. Further more, when I show it in table box, it shows blanks as NULL.

Now when I write if(Field='NULL','Y','N') I get Y for blanks.

I am not able to understand as to why this is happening. I know it has to do with the spaces coming form the excel but I am not able to fix it other than writing Field='NULL'

Re: Handling Nulls and Blank fields

adamdavi3s — Fri, 31 Mar 2017 07:56:07 GMT

Can you share your excel sheet, or a sample of it?

Sounds like there might be some unprintables in there

try len(keep(field,'abcdefghijklmnopqrstuvqxyz012345679')) and see what that gives you

Re: Handling Nulls and Blank fields

vinieme12 — Fri, 31 Mar 2017 08:07:58 GMT

try

IF(len(Trim(Field))=0,'Blank','NotBlank')

Re: Handling Nulls and Blank fields

Anonymous — Fri, 31 Mar 2017 08:38:08 GMT

Hi,

If you do not want any blank records Set Verbatim=1 in script

But if you want as No & Yes than if(len(Trim(Field))=0.'Y','N')

Thanks

Paridhi

Re: Handling Nulls and Blank fields

Anonymous — Fri, 31 Mar 2017 09:00:48 GMT

when I use len(keepchar(field,'abcdefghijklmnopqrstuvqxyz012345679')) it returns 0 for Nulls or blanks.

Re: Handling Nulls and Blank fields

Anonymous — Fri, 31 Mar 2017 09:03:34 GMT

So I will have write if (len(keepchar(field,'abcdefghijklmnopqrstuvqxyz012345679'))=0,'NA', field).