7 Replies Latest reply: Jan 18, 2017 6:43 AM by Rene Zapf

# Show values where the first 3 characters are Numbers?

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

• ###### Re: Show values where the first 3 characters are Numbers?

Maybe

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

• ###### Re: Show values where the first 3 characters are Numbers?

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

• ###### Re: Show values where the first 3 characters are Numbers?

May be this

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

• ###### Re: Show values where the first 3 characters are Numbers?

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 ','.

• ###### Re: Show values where the first 3 characters are Numbers?

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.

A Primer on Set Analysis

Why is it called Set Analysis?

• ###### Re: Show values where the first 3 characters are Numbers?

One more might be this

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

• ###### Re: Show values where the first 3 characters are Numbers?

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

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)