Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
macaulay
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
sunny_talwar

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
mato32188
Specialist
Specialist

Hi Torn,

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

Capture.PNG

BR

Martin

ECG line chart is the most important visualization in your life.
andrespa
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

devarasu07
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

Chanty4u
MVP
MVP

try this

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

Where IsNull(Field)=0;

sunny_talwar

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

macaulay
Creator
Creator
Author

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.