topic Re: Simple if is killing me in QlikView

Simple if is killing me

Wed, 09 Jan 2013 17:11:46 GMT

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!

Re: Simple if is killing me

fosuzuki — Wed, 09 Jan 2013 17:23:50 GMT

can you post a sample qvw?

Re: Simple if is killing me

Wed, 09 Jan 2013 18:04:27 GMT

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)).

Re: Simple if is killing me

Anonymous — Wed, 09 Jan 2013 18:15:01 GMT

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.

Re: Simple if is killing me

Thu, 10 Jan 2013 14:19:02 GMT

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

Re: Simple if is killing me

Thu, 10 Jan 2013 14:45:46 GMT

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?

Re: Simple if is killing me

Thu, 10 Jan 2013 15:00:58 GMT

For example, is this what you want to see?

Re: Simple if is killing me

Thu, 10 Jan 2013 15:02:37 GMT

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

Re: Simple if is killing me

Thu, 10 Jan 2013 16:07:01 GMT

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.

Re: Simple if is killing me

Fri, 11 Jan 2013 15:10:11 GMT

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

Re: Simple if is killing me

Fri, 11 Jan 2013 16:15:26 GMT

Tor

I'm starting to some of what your problem is, you have full accumulation switched on for these columns which is making life pretty difficult in understanding your data. So, now that I've siwtched this off, I think we might be able to get somewhere with it, but I'm still not absolutely clear on your requirements.

If you are executing an IF statement per cell then this will run against each YM value, so in your source data you have something like this:

YM A1 A2 A3

20081 - 929 -

20082 847 - -

20083 - 472 -

20084 834 767 -

20085 - - 108

The last line is made up, because you don't have anything with data in A3, however, given the above simple dataset, is the following what you want?

YM LatestA

20081 929

20082 847

20083 472

20084 767

20085 108

Or is it something else, maybe the above is correct in terms of individual lines but you want each line to be the cumulative total, or maybe I've got it completely wrong again?

Re: Simple if is killing me

Mon, 14 Jan 2013 08:55:33 GMT

Hi Nigel Thanks for your answer.

IN the case you present Lateset A would be simply 108.

If A3 would have been empty it should only be the data in A2. in other words, Latest a would be:

YM A1 A2 A3

20081 - 929 -

20082 847 - -

20083 - 472 -

20084 834 767 -

20085 - - -

YM Latest A

20081 929

20082 -

20083 472

20084 767

20085 -

So the latset is allways the highest number in A1/2 or 3 but it also has to contain data.

I think this should be a simple if but it allways adds the budgets that contains data for me when i really just want to select and copy one budget, the budgets that contain data and then out of those the one with the highest "name-number"

I hope this clarifies, Thanks a lot for your time and interest and I would be very greatful if you could help me!

Cheers

Tor Lindström

Re: Simple if is killing me

Mon, 14 Jan 2013 09:10:46 GMT

Hi Tor

If this is the case then the solution that I sent on 10th Jan at 11:07 is the one you want to use as this does exactly what you are asking for. Obviously there are things that you are struggling to replicate in your real document but those are what we need to concentrate on, because overall the solution does work as it should (I think).

One of the key things you need to do in your table is to remove the full acumulation on expressions because this is not helping you, it clouds everything.

If you could go back to that solution and take the implementation step by step, we'll try to correct each little problem as it comes up.

Nigel.