Exclude fields, that don't have specific value

[Anonymous]

Hi all together,

I have an excel sheet, that looks like this:

Project	Value	Costs
1	10000	5000
2	20000	12000
3	32000	15000
	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

[mato32188]

Hi Torn,

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

BR

Martin

[andrespa]

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]

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]

try this

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

Where IsNull(Field)=0;

[sunny_talwar · Accepted solution]

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

[Anonymous]

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.