Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions

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
Creator III
Creator III

Hi Torn,

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

Capture.PNG

BR

Martin

Specialist
Specialist

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

Master II
Master II

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

Champion III
Champion III

try this

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

Where IsNull(Field)=0;

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

Creator
Creator

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.