Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

macaulay
Contributor

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

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

6 Replies
mato32188
Contributor 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

andrespa
Valued Contributor

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

devarasu07
Honored Contributor 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

Chanty4u
Esteemed Contributor III

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

try this

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

Where IsNull(Field)=0;

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

macaulay
Contributor

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.

Community Browser