topic Need help with SQL SELECT in QlikView

Need help with SQL SELECT

Wed, 01 Sep 2010 13:01:47 GMT

Hi all,

I need help with a SQL SELECT statement - here it is:


SQL SELECT creationdate as "Order created",
 id_case,
 model as "WL_model",
 "model_name_long",
 "model_name_short",
 service,
 status,
 "subsidiary_registered",
 repairdate

FROM "_lordwh_global".dbo."v_case";

Now i want to replace the id_case with something like that:


IF(ISNULL(Sum(id_case)),0,Sum(id_case))

How can I implement such a function in the SQL select?
I want to replace all NULL-values with a 0 from the id_case.

Thanks for help!

Need help with SQL SELECT

Wed, 01 Sep 2010 14:14:06 GMT

It depends on the database, in Oracle you can use nvl(). Also if you use sum() you will need a group by as well

Need help with SQL SELECT

Wed, 01 Sep 2010 14:14:38 GMT

Hello,

2 ideas:

a) What is your database? For example Oracle offers a function (nvl) which does exactly what you want: (SELECT nvl(id_case,0) ID from ... ).
So check the SQL functions for your data.

b) combine the SQL select with a load statement:

LOAD
if(isnull(id_case),0,id_case) as id_case,
<... other stuff ...>
;
SQL SELECT
id_case,
<... other stuff ...>
FROM ...;

hth,
Thilo

Need help with SQL SELECT

Wed, 01 Sep 2010 14:21:15 GMT

Thank you so much!

That's exactly what i needed 🙂

Need help with SQL SELECT

jonathandienst — Wed, 01 Sep 2010 14:23:09 GMT

MS SQL Server has the COALESCE function, as in

COALESCE (id_case, 0)

which will return 0 is id_case is null, or id_case if it is not.

But if you want to get SUM(id_case) you will probably want some sort of GROUP BY clause (no matter what database server you're using).

Jonathan

Need help with SQL SELECT

Thu, 02 Sep 2010 08:00:33 GMT

Now I'm having another Problem.

Because of the Data from column SR_Number, wheres no Data in column id_case, QlikView is joining the two tables and fills in the emtpy space where column id_case has no values where SR_Number has.

I did this if-clause in the Load script:


if(ISNULL(id_case),0,1) as id_case_null

I can only select the 1 (which means the id_case column has values and is NOT NULL), but i cant select the 0 (which should show only the data when theres no value (NULL) in id_case).

So, I didn't solve my problem 😞

Need help with SQL SELECT

Thu, 02 Sep 2010 08:09:49 GMT

Hi HofbauerAn,

Try this and let me know the outcome:

isnull(Sum(id_case),0) as id_case

Need help with SQL SELECT

Thu, 02 Sep 2010 08:23:19 GMT

Hi Rikab,

here's what I wrote in the script:


load
 *,
 ISNULL(SUM(id_case),0) AS id_case,
 1;
SQL SELECT creationdate as "Order created",
 id_case,
 model as "WL_model",
 "model_name_long",
 "model_name_short",
 service,
 status,
 "subsidiary_registered",
 repairdate

FROM "_lordwh_global".dbo."v_case";

I get the following error:

Error in expression:
IsNull takes 1 parameter

load
*,
ISNULL(SUM(id_case),0) AS id_case,
1

Thank you very much for your help.

Need help with SQL SELECT

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 09:03:41 GMT

Hello,

You probably mean

load If(Len(id_case) > 0, 1, 0) AS id_case, // EDIT: ended correctly with ',' model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdate;SQL SELECT creationdate as "Order created", id_case, model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdateFROM "_lordwh_global".dbo."v_case";

I used "Len(id_case) > 0" on purpose, as I've found that sometimes isnull doesn't behave as expected.

If you only want to load those records where id_case is not null, you may do something like

load *WHERE Len(id_case) > 0;SQL SELECT creationdate as "Order created", id_case, model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdateFROM "_lordwh_global".dbo."v_case";

Hope that helps.

Need help with SQL SELECT

Thu, 02 Sep 2010 09:13:56 GMT

Another Script Error again:


Syntax error, missing/misplaced FROM:
load
 *,
 IF(Len(id_case) > 0, 1, 0)) AS id_case,
 1
load
 *,
 IF(Len(id_case) > 0, 1, 0)) AS id_case,
 1

Like I said before - I only want to show the data in the table, where's no value in id_case.
Thank you very much for your help.
I really appreciate it.

Need help with SQL SELECT

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 09:30:56 GMT

Yep, I fix it now

load If(Len(id_case) > 0, 1, 0) AS id_case, model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdate;SQL SELECT creationdate as "Order created", id_case, model as "WL_model", "model_name_long", "model_name_short", service, status, "subsidiary_registered", repairdate FROM "_lordwh_global".dbo."v_case";

Anyway, that error is usually because of a missing "," or ";" depending on the type of load you are doing.

Regards.

Need help with SQL SELECT

Thu, 02 Sep 2010 09:51:33 GMT

Thank you. It's working now, but I still can't get my result I want.
QlikView won't let me select the NULL values ...

Need help with SQL SELECT

Miguel_Angel_Baeyens — Thu, 02 Sep 2010 09:55:29 GMT

Do otherwise, and use the where clause to select all null values:

WHERE Len(id_case) = 0

If this doesn't work, you will have to check how your database works with null values, and how your odbc/driver returns them.

Regards.