
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
Sum( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3)) = 0"} >} Fieldname2)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe
sum( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3))"} >} Fieldname2)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this
Sum( {$< Fieldname1 = {"=IsNum(Left(Fieldname1,3)) = 0"} >} Fieldname2)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 ','.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
One more might be this
=Sum({$< Fieldname1 = {"=IsText(Left(Fieldname1,3))"} >} Fieldname2)
