topic Re: Do not load blanks in QlikView

Do not load blanks

Tue, 15 Jul 2014 10:37:32 GMT

I’m trying to ensure rows where the Subject field are blank in my data, are not loaded. However, I’ve tried the following terms but they all throw up scripting errors

where not IsNull(Subject) ;

where Len(Trim(Subject))>0 ;

Where Subject is not null ;

Re: Do not load blanks

mdmukramali — Tue, 15 Jul 2014 10:45:27 GMT

Dear,

Where NOT IsNull(Subject);

it is working.

Re: Do not load blanks

ashfaq_haseeb — Tue, 15 Jul 2014 10:47:45 GMT

Hi,

Are you trying this a DB level or what?

Assuming while fetching data from database, try below.

Load

Field1,

Field2,

Subject,

Field3;

sql select * from table

where Subject is not null;

Regards

ASHFAQ

Re: Do not load blanks

Tue, 15 Jul 2014 10:52:18 GMT

Mohammed, I get this error when I try that

Re: Do not load blanks

Tue, 15 Jul 2014 10:54:35 GMT

Ashfaq, I am loading straight from the Qlikview to Salesforce connector.

I have tried your suggestion. This is my script

QUALIFY *;

UNQUALIFY OwnerId;

UNQUALIFY ActivityId;

UNQUALIFY Activity.CalendarDate;

Activities:

LOAD Id AS ActivityId,

WhoId AS Activity.ContactId,

WhatId,

Subject,

ActivityDate AS Activity.CalendarDate,

OwnerId,

Description,

Type,

AccountId,

number_of_individual_activities__c,

Activity_type__c;

SQL SELECT *

FROM Task

WHERE Type NOT IN ('Email','Call','RM/ Bloomberg Chat','Minor communication') AND Status = 'Completed' AND NOT IsNull(Subject);

UNQUALIFY *;

Re: Do not load blanks

ashfaq_haseeb — Tue, 15 Jul 2014 10:59:06 GMT

Hi,

Try below

QUALIFY *;

UNQUALIFY OwnerId;

UNQUALIFY ActivityId;

UNQUALIFY Activity.CalendarDate;

Activities:

LOAD Id AS ActivityId,

WhoId AS Activity.ContactId,

WhatId,

Subject,

ActivityDate AS Activity.CalendarDate,

OwnerId,

Description,

Type,

AccountId,

number_of_individual_activities__c,

Activity_type__c;

SQL SELECT *

FROM Task

WHERE Subject is not null and

(Type NOT IN ('Email','Call','RM/ Bloomberg Chat','Minor communication') AND Status = 'Completed') ;

UNQUALIFY *;

Re: Do not load blanks

Tue, 15 Jul 2014 11:09:39 GMT

Error again

Re: Do not load blanks

hic — Tue, 15 Jul 2014 11:16:19 GMT

It depends on whether the WHERE -clause is in a Load (interpreted by QlikView) or in a SELECT (interpreted by the ODBC driver).

I would suggest using a preceding Load, like the following:

Load * Where Len(Trim(Subject))>0;

SQL SELECT * FROM Task ;

HIC

Re: Do not load blanks

mdmukramali — Tue, 15 Jul 2014 11:18:05 GMT

Dear,

can you try,

where Subject<>' ';

your scenario:

WHERE Type NOT IN ('Email','Call','RM/ Bloomberg Chat','Minor communication') AND Status = 'Completed' AND

Subject<>' ' ;

Re: Do not load blanks

Tue, 15 Jul 2014 11:39:54 GMT

Thanks Henric. Is this what you meant?

QUALIFY *;

UNQUALIFY OwnerId;

UNQUALIFY ActivityId;

UNQUALIFY Activity.CalendarDate;

Activities:

LOAD Id AS ActivityId,

WhoId AS Activity.ContactId,

WhatId,

Subject,

ActivityDate AS Activity.CalendarDate,

OwnerId,

Description,

Type,

AccountId,

number_of_individual_activities__c,

Activity_type__c

WHERE Type NOT IN ('Email','Call','RM/ Bloomberg Chat','Minor communication') AND Status = 'Completed' AND Len(Trim(Subject))>0 ;

SQL SELECT *

FROM Task;

UNQUALIFY *;

Unforatunately I get a 'garbage' load error when I try this

Re: Do not load blanks

martynlloyd — Tue, 15 Jul 2014 11:44:33 GMT

Hi Alex,

Just to point out that Null and Blank are not the same thing...

Did Hendric's solution work?

Re: Do not load blanks

johnca — Tue, 15 Jul 2014 11:50:18 GMT

Hi Alex,

The syntax for not equal to in Salesforce is != so your where clause should contain

Where Subject != Null

--john

Re: Do not load blanks

hic — Tue, 15 Jul 2014 12:16:51 GMT

Not quite...

The "NOT IN" operator is a SQL operator that doesn't work in a Load, so you need to replace that with a Match:

not Match(Type, 'Email','Call','RM/ Bloomberg Chat','Minor communication')

When that is done, it should work.

HIC

Re: Do not load blanks

Tue, 15 Jul 2014 12:48:58 GMT

Thanks again Henric.

Your help is greatly appreciated.

Re: Do not load blanks

Tue, 15 Jul 2014 12:49:04 GMT

yes