Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Do not load blanks

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 ;






1 Solution

Accepted Solutions
hic
Former Employee
Former Employee

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

View solution in original post

14 Replies
mdmukramali
Specialist III
Specialist III

Dear,

Where NOT IsNull(Subject);

it is working.

ashfaq_haseeb
Champion III
Champion III

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

Not applicable
Author

Mohammed, I get this error when I try that

error.png

Not applicable
Author

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 *;

ashfaq_haseeb
Champion III
Champion III

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 *;

Not applicable
Author

Error again

error2.png

hic
Former Employee
Former Employee

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

mdmukramali
Specialist III
Specialist III

Dear,

can you try,

where Subject<>' ';

your scenario:

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

Subject<>' ' ;

Not applicable
Author

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