Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Exclude fields, that don't have specific value

Hi all together,

I have an excel sheet, that looks like this:

ProjectValueCosts
1100005000
22000012000
33200015000
62000

32000

I load the file directly as an excel file, so I don't use the data editor.

I want to exlude from all evaluations the projects, that don't have a Number (so in my example row 4).

I thought about using if (len(Project)=1), ....

but if this would work I don't know how to go on. But maybe there is a better way, you could tell me.

Thanks for the replies,

Tom

Tags (3)
1 Solution

Accepted Solutions
Highlighted

Re: Exclude fields, that don't have specific value

If you don't want to use data load editor, the best way would be to use a calculated dimension in this case....

If(Len(Trim(Project)) > 0, Project)

and then make sure to uncheck 'Include Zero Values' within your dimension to exclude zero or nulls from the dimension above

View solution in original post

6 Replies
Highlighted
Creator III
Creator III

Re: Exclude fields, that don't have specific value

Hi Torn,

go to script editor and write where statement. See below:

Capture.PNG

BR

Martin

Highlighted
Specialist
Specialist

Re: Exclude fields, that don't have specific value

You can try what Martin just said, but if that doesn't work try :

LOAD

     Project,

     Value,

     Costs

FROM [your_excel]

WHERE Project <> ' ';


Sometimes QS loads as some weird blank value, instead of a null value.


Hope it helps,

Andrés

Highlighted
Master II
Master II

Re: Exclude fields, that don't have specific value

Hi,

you can try like below,

LOAD * INLINE [

    Project, Value, Costs

    1, 10000, 5000

    2, 20000, 12000

    3, 32000, 15000

    ,62000, 32000

] where len(Project)>0;

Method 2:

create calculated dim:

=if(isnull(Project) or len(Project)>0, Project)

remember to suppress null value

Method 3:

by using set analysis

sum({<Project={"=len(Project)>0"}>}Value)

sum({<Project={"=len(Project)>0"}>}Costs)

Thanks,
Deva

Highlighted
Champion III
Champion III

Re: Exclude fields, that don't have specific value

try this

From................................

Where IsNull(Field)=0;

Highlighted

Re: Exclude fields, that don't have specific value

If you don't want to use data load editor, the best way would be to use a calculated dimension in this case....

If(Len(Trim(Project)) > 0, Project)

and then make sure to uncheck 'Include Zero Values' within your dimension to exclude zero or nulls from the dimension above

View solution in original post

Highlighted
Creator
Creator

Re: Exclude fields, that don't have specific value

Thanks for all the replies, as I don't want to use a load script I think Sunny's answer will work the best. But I will try the other solutions when needed, I think they will all work.