topic Re: filtering missing values and insert a character string if the condition is met in QlikView

filtering missing values and insert a character string if the condition is met

YoussefBelloum — Fri, 04 Sep 2015 11:20:01 GMT

Hi guys, it's my first time here.

here is my problem:

I'm loading some fields in a dimension table:

SQL SELECT

Code as [% Commercial ID],

Name as [Commercial name]

FROM ...

and the problem is that there are some missing values in the code field when I'm loading, resulting to this:

I just want to insert and display 'NA' for this case into my table. I don't know what I should use and how to use it between an If statement (into the load block) or a CASE (into sql block) ?

Thank you

Re: filtering missing values and insert a character string if the condition is met

Gysbert_Wassenaar — Fri, 04 Sep 2015 13:47:37 GMT

Try a preceding load:

LOAD

If( len(trim(Code)),Code, 'NA') Code as [% Commercial ID],

[Commercial name];

SQL SELECT

Code,

Name as [Commercial name]

FROM ...

Re: filtering missing values and insert a character string if the condition is met

malini_qlikview — Fri, 04 Sep 2015 14:07:51 GMT

Hi,

you can use NullasValue function

eg:

NullasValue Code;

Set NullValue='NA';

Load

SQL SELECT

Code,

Name

From ...

Re: filtering missing values and insert a character string if the condition is met

YoussefBelloum — Fri, 04 Sep 2015 14:15:33 GMT

Thank you so much Gysbert ! It was perfect.

Re: filtering missing values and insert a character string if the condition is met

YoussefBelloum — Fri, 04 Sep 2015 14:17:27 GMT

thank you Malini ! I tried the solution provided by Gysbert and it's working, I'll try the null value function.