topic Re: Show values where the first 3 characters are Numbers? in QlikView

Show values where the first 3 characters are Numbers?

Wed, 18 Jan 2017 09:03:33 GMT

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?

swuehl — Wed, 18 Jan 2017 10:28:22 GMT

Maybe

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

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

Wed, 18 Jan 2017 10:43:32 GMT

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

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

sunny_talwar — Wed, 18 Jan 2017 11:01:28 GMT

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)

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

sunny_talwar — Wed, 18 Jan 2017 11:18:29 GMT

May be this

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

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

Wed, 18 Jan 2017 11:37:26 GMT

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?

swuehl — Wed, 18 Jan 2017 11:38:22 GMT

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.

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

sunny_talwar — Wed, 18 Jan 2017 12:14:53 GMT

One more might be this

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