topic Problems with zeros in value for Expression in QlikView

Problems with zeros in value for Expression

Thu, 22 Nov 2012 23:12:24 GMT

Hello,

I'm dying for some help with an expression I've written in a chart. The field has mixed values, some are only numbers and others also contain a letter in the beginning. Like this:

500020

D345737

But there is one value that is causing me headaches; D000158. I want to eliminate that value but it simply will not work!

The expression is:

if(Orderklantnummer=('D000158'),Null(), Sum({$<OrderJaar={$(=Only(OrderJaar))}>}Bedrag))

If I use the value 500020 or D345737 here it works oke, but as soon as I put the value I need in nothing happens.

Hope someone can illuminate me here because I have just been starting to work with QV (11)

Re: Problems with zeros in value for Expression

Fri, 23 Nov 2012 03:28:20 GMT

did you try using Orderklantnummer inside the set statement like.....

Sum({$<OrderJaar={$(=Only(OrderJaar))}, Orderklantnummer={"<>D*"}>}Bedrag)

this will exclude all Orderklantnummer that have a preceding letter D.

Re: Problems with zeros in value for Expression

Fri, 23 Nov 2012 07:44:43 GMT

The problem is that I only want to exclude this number. I tested it with another nr that has a D in front and that worked. So I think the zeros are causing the problem. Thanks for the help, any more suggestions?

Re: Problems with zeros in value for Expression

jagan — Fri, 23 Nov 2012 09:54:05 GMT

Hi,

Try this expression

=Sum({$<Orderklantnummer-={'D000158'}, OrderJaar={$(=Only(OrderJaar))}>}Bedrag)

Regards,

jagan.

Re: Problems with zeros in value for Expression

Fri, 23 Nov 2012 16:22:56 GMT

if its only one value then you can exclude it as mentioned in the previous mail. That should work just as fine as substituting the exclusion with an alternative like below.....

Sum({$<Orderklantnummer={"<>D000158"}, OrderJaar={$(=Only(OrderJaar))}>}Bedrag)

Re: Problems with zeros in value for Expression

Fri, 23 Nov 2012 21:53:40 GMT

Jasleen,

Your last suggestion did the trick! Thanks.

The solution that Jagan gave also eliminated some of the values, but left a syntax error in the formula (the red curly line under the formula no?). Thanks for the help too.

Re: Problems with zeros in value for Expression

Sat, 24 Nov 2012 08:06:20 GMT

Sorry, I was too soon with my joy. The solution you gave seems to exlude all possibilities containing a D-Value anyway

Regards,

Jan

Re: Problems with zeros in value for Expression

jagan — Sat, 24 Nov 2012 09:49:52 GMT

Hi,

This expression

=Sum({$<Orderklantnummer-={'D000158'}, OrderJaar={$(=Only(OrderJaar))}>}Bedrag)

works as you expected, qlikview shows error for -=, but it works.

From Qlikview help file.

sum( {$<Product += {OurProduct1, OurProduct2} >} Sales )

for excluding

sum( {$<Product -= {OurProduct1, OurProduct2} >} Sales )

returns the sales for the current selection, but using an implicit union to add the products “OurProduct1” and “OurProduct2” to the list of selected products.

Hope this helps you.

Regards,

Jagan.

Re: Problems with zeros in value for Expression

Sat, 24 Nov 2012 21:17:53 GMT

Jagan,

You are right, this works fine. Thanks for your help.

Regards,

Jan