Simple if is killing me

[#1]

Hi,

Im quite new to QlikView but experienced in Excel. I am trying to make a simple If statement but it is killing me!

I have created different expressions that are displayed in a linechart but i am trying to make an if expresseion that will filter out some of them.

When i write:

if(B3>0,B3,B2)

B3 is more then 0 so it should now only display a new line that is excatcly like B3. If B3 would be 0 or less it should make a new line that is exactly like B2.

It doesnt, it instead adds B3+B2 in my new line.

At the same time, if B3 is less then 0 then it only displays B2

Why does it do B3+B2??? when the condition is true in if(B3>0,B3,B2) when the condition is false it does as it should, only shows B2.

(I am also using QlikView 10)

Thanks!

[fosuzuki]

can you post a sample qvw?

[#3]

Is this the whole expression you're using, or do you have SUM() wrapped around it somehow?

OR, are you saying that each "line" will only ever have one value of B3 & B2?

If there are multiple values and you want to have a sum of B3 or B2 for each row in the group, depending upon the higher value in each row, then you should use Sum(If(B3>0,B3,B2)).

[Anonymous]

To answer the question, it would be helpful to see the data model, and the chart dimensions.
You're not using aggregation functions.  Is there a possibility that there are multiple B3 field values per chart dimension?  I'd expect to see expresion like this:
sum(if(B3>0,B3,B2))

As Fernando Suzuki suggested, post an example.  I'm sure it will be easy to resolve.

[#5]

Hi, thanks for the help but it didn’t work.

I have made a test file now since I cant hand out the original data. See attatchment.

What I want to do is to make an expression that chooses what lines to display. Each of the expressions/lines representst different versions of a budget.

So only the latest of A1, A2 or A3 should be displayed. The original database is made up of many different products and this data is only test selection of a product.  Based on the product selected I only want to show the latest budget for each letter, A,B or C.

If A3 is empty but A2 contains data, then I only want to display A2 and not A1 which will contain data.

In the final version I will make all the expressions/lines invisible and then use an expression that chooses the latest budget that contains data.

In the file I use the Expression LatestA =  If(A3>0,A3,if(A2>0,A2,A1))

I think that this should only display A2 since A3 I zero. But instead it adds A2 and A1.

Help is much appreciated!

Cheers,

Tor Lindström

[#6]

Hi

Can I just clarify then, when you say you want the latest budget, the latest as far as I can see from your data is:

A1 = 201006 at a value of 585

A2 = 201009 at a value of 498+122

A3 has no values

B1 = 201011 at a value of 781+179

B2 = 201010 at a value of 361+128

etc, so is the above what you are expecting to see?

[#7]

For example, is this what you want to see?

[#8]

Thanks for reply Nigel,

The order comes from the number, so A3 is newer then A2, which is newer then A1. So in the case of A. I only want to display A2 for all dates. Since there is no data in A3.

My problem is that it adds A2 and A1 with the code i am running, see start post.

When I say "latest" doesnt have anything to do with the dates such as "201006", it is the latest version of a planning stage. So when production planning is made, different budgets are changed througout the planning process.

So I want to see the A/B or C with the highest number and that also contains data.

I hope this clarifies.

Your help is much appreciated !

Tor Lindström

[#9]

Hi Tor

The attached works (I think).

Load script has slight mods.

3 variables created, these allow me to identify the max value of YM2 for each of A1, A2, A3 (so you'd need to do the same for B1,B2,B3 and C1,C2,C3 to complete it.

The Latest A expression in your chart now uses the above variables and correctly shows only A2.

You should check the logic of the LatestA expression though, to make sure I have understood correctly.

Cheers,

Nigel.

[#10]

Thank you very much for your help Nigel!

I don’t really understood all the things that you have done but have tried and it is not working. I only get nulls.

The file I sent is a test and the real version varies some. The data I use is from a much larger database that someone else has built up and I am just suppose to do some adjustments which seems to be much more difficult than I expected.

I have problems with fixing the YM2 field in the script. And it says YM2 bad fieldname when I try to create the “BudgetXY” variables.

What I originally thought would be a simple nested If-statement has turned into something much larger and more complex.

But even in the Example I cant get the C budget to work. It works for B though?

I have again done the nested If function as I would like it to be, (wishful thinking?) the weird thing is that when it reaches the true condiction it then executes the “then” statement but then it also continues to move down to the next condition. In all other programs I have worked with once an If statement reaches a true condition I excecutes the “then” command and then ends the if.

//see attatchmet

How do I make the if statement stop running after it reaches a true condition. Instead it keeps on adding all true condition, in the file you can see, the new expressions I made in the chart.

All I want to do really is to display the budget type with the higest number that contains data.

I really hope you have the time to help me out!

Cheers, hope you have the tme to help me out!

Tor Lindström