Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
pzwonford
New Contributor II

Load if

Hi guys - new to QlikView!

I have an access database with the following files:

Company name, payment due, payment received

I would like to load only those who have an empty value in the 'payment received' column.

I've tried a number of solutions including

SELECT *

FROM `DATABASE.mdb`.`Company info`;

where [payment received] IsNull;

I'm having no luck. Can anyone help me?

1 Solution

Accepted Solutions
rajni_batra
Valued Contributor

Re: Load if

SELECT *

FROM `DATABASE.mdb`.`Company info`;

where [payment received] Is Null;

ur format is correct i tink the issue must be of space in bw is null

Hope this could be the reason

6 Replies
Not applicable

Re: Load if

Hi,

You could do this

LOAD *;

SQL SELECT *

FROM 'Company info' where isnull([payment received);

And also a question (when this happen to be not the answer): does your SQL query work _without_ that isnull filter?

#edit: Updated it (old situation I had not(isnull( paymentReceived)) while you were looking for only those records where that field is null.

I think this should be correct SQL syntax for access. It could be that the values are not NULL but an empty string in the sourcedatabase. Then use the len() function.

Hope it helps

Not applicable

Re: Load if

Dear ,

You shoud try this

SELECT *

FROM `DATABASE.mdb`.`Company info`;

where len(trim([payment received] ))=0;


May be this work because some time in Field value a space character available

so that its not return with isnull.

rajni_batra
Valued Contributor

Re: Load if

SELECT *

FROM `DATABASE.mdb`.`Company info`;

where [payment received] Is Null;

ur format is correct i tink the issue must be of space in bw is null

Hope this could be the reason

MVP
MVP

Re: Load if

Hi

The SELECT statement in SQL SELECT is handed over to the server for execution, so the null check must be in the correct syntax for your database server. If you are using SQL Server, then rani.batra's script should work (don't forget the SQL in front of th SELECT), although I would do away with the backquotes. If you are using another DBMS, then the syntax may vary.

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
pzwonford
New Contributor II

Re: Load if

Thanks for your help guys - i did this..

Load

[Company Name]

where

Isnull([Payment Received]);

SQL Select *

FROM [Database];

Unqualify*;

Not applicable

Re: Load if

Hehe, nice that you have a working solution.

However when there are a lot of records in your database where paymentReceived = NULL, then this is not the most optimal solution.

The SQL Select-part you currently use loads ALL records from that table from the database... and in the load .... Where part, you filter by isnull(paymentReceived)

I would prefer to have the Where-clause in the SQL select part for that reason.          

Community Browser