Show values where the first 3 characters are Numbers?

[#1]

Hello Qlik Community,

as stated in the title i am trying to get only the values where the first 3 characters are numbers.
What i know already is that i can use
IsNum(Left(FieldName,3)) to check if the statement is true.

I already tried this, but something in my function is wrong.
sum( {$<IsNum(Left(Fieldname1,3)) = 0>} Fieldname2)

Note: I am using this this expression for a chart if that is any valuable information.

greetings,
rene

[swuehl]

Maybe

sum( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3))"} >} Fieldname2)

[#3]

Thank you for your quick reply Stefan,
i think your answer is the way to go.

The only problem now is that i want to get the Sum of "Fieldname2".

I have tried something like

( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3))"} >} Sum(Fieldname2))

which results in an error.

To give you a better understanding of what i need, here's and example of how my table looks like:

I have different sections, and the one's that i don't want to be calculated in are the sections that start with a 3 digit number.
What i need is the sum of the hours, where the project doesn't start with a 3 digit number.
So in this case 4.00 + 3.00 = 7.00 .

greetings,

rene

[sunny_talwar]

Another approach could be to create a flag in the script.

LOAD Fieldname1,

          Fieldname2,

          If(IsNum(Left(FieldName,3)), 1, 0) as Flag

FROM....

and then this

Sum({<Flag = {1}>} Fieldname2)

or this if you want the selections to work for the true condition

Sum({<Flag *= {1}>} Fieldname2)

[sunny_talwar · Accepted solution]

May be this

Sum( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3)) = 0"} >} Fieldname2)

[#6]

Thanks a lot Sunny,

this seems to do the trick.

Is it possible to add another condition to the statement?

Like ...and Fieldname3<>'FieldvalueXY'.

If yes, how would i approach adding more conditions?

greetings

Edit: I figured it out, i can just add another condition using a ','.

[swuehl]

Rene Zapf wrote:

I have tried something like

( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3))"} >} Sum(Fieldname2))

You need to add the set expression (the part in curly brackets to an aggregation function:

Sum( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3))"} >} Fieldname2)

Also note that only field names are allowed left of the equal sign in a set modifier (analog to making selections in fields).

If you want to only consider the pure text values of fieldname1 (which is not what you asked for initially), other options could be maybe

=Sum( {$< Fieldname1 = {"=NOT IsNum(Left(Fieldname1,3))"} >} Fieldname2)

=Sum( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3))=0"} >} Fieldname2)

=Sum( {$< Fieldname1 -= {"=IsNum(Left(Fieldname1,3))"} >} Fieldname2)

etc.

See also

A Primer on Set Analysis

Why is it called Set Analysis?

[sunny_talwar]

One more might be this

=Sum({$< Fieldname1 = {"=IsText(Left(Fieldname1,3))"} >} Fieldname2)