Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

7 Replies
swuehl
MVP
MVP

Maybe

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

Not applicable
Author

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:


exampleTable.PNG

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

May be this

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

Not applicable
Author

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
MVP
MVP

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)