Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Employee
Employee

Re: Do not load blanks

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

14 Replies
mdmukramali
Valued Contributor II

Re: Do not load blanks

Dear,

Where NOT IsNull(Subject);

it is working.

Re: Do not load blanks

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

Re: Do not load blanks

Mohammed, I get this error when I try that

error.png

Not applicable

Re: Do not load blanks

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

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

Re: Do not load blanks

Error again

error2.png

Employee
Employee

Re: Do not load blanks

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
Valued Contributor II

Re: Do not load blanks

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

Re: Do not load blanks

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

Community Browser