topic Re: Do not load record with null field in QlikView

Do not load record with null field

Thu, 09 Oct 2014 13:44:20 GMT

How can i adjust my load script to ensure the recorded without an AccountId are NOT loaded

Both these expressions kick up errors. What exactly am I doing wrong?

WHERE NOT IsNull(AccountId);

and

WHERE len(trim(AccountId))>0;

Re: Do not load record with null field

Anonymous — Thu, 09 Oct 2014 13:53:41 GMT

You can try

WHERE IsNull(AccountId) = false() OR len(trim(AccountId))>0;

Both on the where.

Re: Do not load record with null field

cspencer3 — Thu, 09 Oct 2014 13:54:41 GMT

Try out

Table:

Load *

RESIDENT OtherTable

Where IsNull(AccountId) <> -1

;

Re: Do not load record with null field

cspencer3 — Thu, 09 Oct 2014 14:01:14 GMT

Upon testing the:

Where IsNull(AccountID) <> -1

seemed to work for me.

Re: Do not load record with null field

Thu, 09 Oct 2014 14:06:02 GMT

Didn't work. Got the error

"Invalid aggregate function IsNull"

Re: Do not load record with null field

Thu, 09 Oct 2014 14:06:44 GMT

Didn't work. Got the error

"Invalid aggregate function: IsNull"

Re: Do not load record with null field

jonathandienst — Thu, 09 Oct 2014 14:07:59 GMT

Is this criterion in a SQL SELECT statement?

SQL SELECT ......

WHERE len(trim(AccountId))>0;

This does not work because this statement is executed on the database server, not by QV. Translate the statement to the SQL dialect of your server; eg for MS SQLS

SQL SELECT ......

WHERE AccountId IS NOT NULL;

HTH

Jonathan

Re: Do not load record with null field

anbu1984 — Thu, 09 Oct 2014 14:08:09 GMT

Can you post entire script

Re: Do not load record with null field

Anonymous — Thu, 09 Oct 2014 14:08:19 GMT

Could you paste your load here?

Regards,

Gabriel

Re: Do not load record with null field

cspencer3 — Thu, 09 Oct 2014 14:11:16 GMT

Yeah, as jonathan said, if you are executing this in SQL this will definitely not work.

Re: Do not load record with null field

Thu, 09 Oct 2014 14:16:40 GMT

Yes, youre right Jonathan. It is

the complete script is

SQL SELECT *

FROM Contact

WHERE RecordTypeId NOT IN ('012w000000063XQ','012w000000063qu') AND AccountId IS NOT NULL;

Apologies Charles and Anbu for not being a little clearer. I should have posted the entire script

However I now get this error error

Re: Do not load record with null field

anbu1984 — Thu, 09 Oct 2014 14:21:03 GMT

Which database are you connecting to?

Re: Do not load record with null field

Thu, 09 Oct 2014 14:22:17 GMT

SalesforceDLL.dll

Re: Do not load record with null field

anbu1984 — Thu, 09 Oct 2014 15:29:43 GMT

Try

WHERE AccountId = NULL;

Re: Do not load record with null field

antoniotiman — Thu, 09 Oct 2014 16:15:04 GMT

Hi Alex,

have You tried with Preceding Load Sintax ?

Like :

Load *

Where Not IsNull(Field);

SQL Select *

From Table;

Re: Do not load record with null field

Fri, 10 Oct 2014 07:42:22 GMT

I assume you meant

WHERE NOT AccountId = NULL;

That gives the error

ERROR at Row:2:Column:68 unexpected token: 'NOT'

Re: Do not load record with null field

anbu1984 — Fri, 10 Oct 2014 07:54:44 GMT

For Not Null, try this

WHERE AccountId != NULL;

Re: Do not load record with null field

Fri, 10 Oct 2014 08:02:13 GMT

This worked!!!

thanks

Re: Do not load record with null field

anbu1984 — Fri, 10 Oct 2014 08:06:17 GMT

You can use Not and IsNull()

Where Not(IsNull(AccountId ))

If you are looking for where AccountId is not blank, then use this

Where AccountId != ''

Where Not(IsBlank(AccountId ))