Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

YoussefBelloum
Esteemed Contributor

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

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:

test.png

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

1 Solution

Accepted Solutions

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

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


talk is cheap, supply exceeds demand
4 Replies

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

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


talk is cheap, supply exceeds demand
malini_qlikview
Contributor II

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

Hi,

you can use NullasValue function

eg:

NullasValue Code;

Set NullValue='NA';

Load

*;

SQL SELECT

Code,

Name

From ...

YoussefBelloum
Esteemed Contributor

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

Thank you so much Gysbert ! It was perfect.

YoussefBelloum
Esteemed Contributor

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

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

Community Browser