Problems with zeros in value for Expression

[#1]

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)

[#2]

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.

[#3]

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?

[jagan]

Hi,

Try this expression

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

Regards,

jagan.

[#5]

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)

[#6]

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.

[#7]

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

Regards,

Jan

[jagan · Accepted solution]

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.

[#9]

Jagan,

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

Regards,

Jan