17 Replies Latest reply: Jul 12, 2011 12:28 PM by Nicolas MARTIN

# Using "if" in an expression : how to have distinct values ?

Good day,

I have the following data :

```Data:
LOAD * INLINE [
Country, City, Value
France, Paris, 1
France, Lyon, 2
USA, Washington, 4
USA, New York, 8
Japan, Tokyo, 16
];

```

I'm using a separate table to manage geography :

```Geography:
Country AS Country_dim,
City AS City_dim
RESIDENT Data;

```

When I do a simple graph using the "Country" dimension, I have the following correct result :

with a "sum(Value)" formula.

When I do more complex graphics, I need to use the "Country_dim" dimension as graph dimension (instead of "Country"), using a "if" in my expression

```sum(if(Country_dim = Country, Value))
```

Because "Country_dim" have a frequency > 1, I have the wrong values :

The solution I found is to make a division :

```sum(if(Country_dim = Country, Value))
/
count(Country_dim)

```

but my total goes wrong :

So, I'm using a more complex formula as workaround :

```if(dimensionality() = 1,
sum(if(Country_dim = Country, Value))
/
count(Country_dim),

if(dimensionality() = 0,
sum(Value)
)
)

```

that gives me the expected result :

BUT, this workaround can give me very very complex formulas when I have to use many dimensions, and this formulas can be very very long to compute (because the "if" make a calculation of every pieces of formula).

Is there a way to say "I want to make my test on the distinct values of Country_dim" ?

I hoped that "sum(if(only(Country_dim) = Country, Value))" would work, but it doesn't.

• ###### Using "if" in an expression : how to have distinct values ?

Hi Nicolas,

Would an easier workaround be to have separate tables for your dimensions? As in:

Geography_Country:

DISTINCT Country AS Country_dim

RESIDENT Data;

Geography_City:

DISTINCT City AS City_dim

RESIDENT Data;

That gives me:

and

Regards,

Chris

• ###### Using "if" in an expression : how to have distinct values ?

Ah, pictures missing!

and

• ###### Re: Using "if" in an expression : how to have distinct values ?

In my example, there is only 1 dimension.

In real life, I have more than 1 dimension, and dimensions are related ("Country", "City", ...).

If I use separate tables, I have to add a lot of conditions to filter the data.

Something like

```sum({\$ <Country=P(Country_dim), City=P(City_dim)>} if(City_dim = City and Country_dim = Country, Value))
```
• ###### Re: Using "if" in an expression : how to have distinct values ?

Hi Nicholas,

I've attached the qvd I have amended. I have only made changes in the script, I have not made any changes to the formula you have used.

Hopefully that gives you a better idea of what I have done rather than the 2 pictures and bit of code.

Regards,

Chris

• ###### Re: Using "if" in an expression : how to have distinct values ?

Chris Abwat-Johnson a écrit:

I've attached the qvd I have amended. I have only made changes in the script, I have not made any changes to the formula you have used.

Unfortunately, your solution give up the relation between the dimensions.

If I want to display a graph with 2 dimensions, if I don't check "suppress zero values", I have the following result :

Yes, I could check the "suppress zero values", but when I will have a formula that doesn't return "null" or "0", I will have all my cities in every country. This is not OK for me.

With your solution, I have to add another test in my expression to check if the City_dim have to be in relation with the current Country_dim.

• ###### Using "if" in an expression : how to have distinct values ?

I've added this bit of code to the end of the script:

left Join (Geography_City) LOAD

City as City_dim,

Country as Country_dim

resident Data;

It is putting the country code into the Geography_City table to make a relation between the Geography_City and Geography_Country tables.

I can then re-check the Supress Zero Values.

Not sure if this will get a bit complicated though when you start adding more dimensions...

Regards,

Chris

• ###### Re: Using "if" in an expression : how to have distinct values ?

Chris Abwat-Johnson a écrit:

I've added this bit of code to the end of the script:

left Join (Geography_City) LOAD

City as City_dim,

Country as Country_dim

resident Data;

This gives the same result as my first post : some "Country_dim" have a frequency > 1.

• ###### Re: Using "if" in an expression : how to have distinct values ?

Hello Nicolas,

In the "Wrong Values" chart set Country as dimension instead of Country_dim and the following as expression

```Sum({< Country = P(Country_dim) >} Value)
```

The P() function gets all possible values of the field passed as parameter (Contry_dim). The whole comparison is called set analysis (between curly brackets). You need to do it this way because the Value field is related to the Country dimension, and because the expression aggregates in regards to the value in the dimension.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: Using "if" in an expression : how to have distinct values ?

Miguel Angel Baeyens a écrit:

Hello Nicolas,

In the "Wrong Values" chart set Country as dimension instead of Country_dim and the following as expression

``````Sum({< Country = P(Country_dim) >} Value)
``````

This is not what I need.

If I could use the "Country" dimension in my graph, I would. But in my question, I can't.

(in my real application, my expression are far more complex with set analysis, but I prefer to post a "simple" question not explaining my whole application. Fact is : I have to use "Country_dim" as dimension)

Moreover, if I use "Country" as dimension and "Sum({\$ < Country = P(Country_dim) >} Value)" as expression, the user can no more make his selection directly in the graph.

• ###### Re: Using "if" in an expression : how to have distinct values ?

Hi,

Again, tables are not linked, so the aggregation is not likely to work as expected unless some relationship is established between them.

Anyway, a very poor implementation that is likely to work is as follows:

```Sum(DISTINCT Value * (Country_dim=Country) * -1)
```

DISTINCT will not sum Values if they are duplicated (on for each Country / Country_dim value). The parenthesis will return -1 (true) if the are the same, and multiplied by -1 again to get the proper result.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: Using "if" in an expression : how to have distinct values ?

Miguel Angel Baeyens a écrit:

Again, tables are not linked, so the aggregation is not likely to work as expected unless some relationship is established between them.

Anyway, a very poor implementation that is likely to work is as follows:

``````Sum(DISTINCT Value * (Country_dim=Country) * -1)
``````

DISTINCT will not sum Values if they are duplicated (on for each Country / Country_dim value). The parenthesis will return -1 (true) if the are the same, and multiplied by -1 again to get the proper result.

If I use master tables, it is because I don't want to have relation between my tables !

With your solution, if I replace

```France, Paris, 1
```

with

```France, Paris, 0.5
France, Paris, 0.5

```

my result are false...

I cannot assure that there is not 2 values alike in my data.

• ###### Re: Using "if" in an expression : how to have distinct values ?

Hi,

Yes, that's right. DISTINCT in Sum() will distinguish any value regardless its source, hence I mentioned that was not a good idea. Anyway, you need conditionals since you are working row by row, and set analysis is evaluated once and for the whole chart (and not value by value), so according to your data the following will work

```Sum(If(Country = Country_dim AND City = City_dim, Value))
```

Because you are taking all possible dimensions in the expresion (getting to the lower possible level of detail for the model provided). When you select a country in your Country listbox, the results with this expression will be fine, and the grand total will show the proper value as well. But you will need to add any other possible dimension in the left part of the condition with AND, so it still may take a lot to render, depending on the number of records. Actually, I'd dare say that not having the tables linked (which is not possible in your case) will never perform as if they were, because the conditional forces to compare row by row each possible value of the dimensions used in the chart.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Using "if" in an expression : how to have distinct values ?

Hi Miguel,

I see what you are saying now, it took a while to sink in.

My first idea was that Nicolas may be looking for data islands to get around the problem he is having.

Obviously you will have more experience with this than me as I've only been using QlikView for 9 months so you will be able to warn of all of the pitfalls and things to bear in mind when doing this!

Regards,

Chris

• ###### Using "if" in an expression : how to have distinct values ?

Hello,

Adding further to your query, I was wondering why can you to something like

`If(Country = Country_dim AND City = City_dim, sum(Value))or`

IF(Country = Country_dim, sum(Value))

Please correct me if I am wrong here.

• ###### Using "if" in an expression : how to have distinct values ?

This cannot work.

You aks "if the dimension Coutry equals the dimension Coutry_dim, then give me the sum of all Values".

"Country" is not equal to "Country_dim", so your condition is never true.

If it was, "sum(Value)" would return the sum of all values.

• ###### Re: Using "if" in an expression : how to have distinct values ?

Did you try using the conditional within instead? I've posted above about that.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

• ###### Re: Using "if" in an expression : how to have distinct values ?

Miguel Angel Baeyens a écrit:

Did you try using the conditional within instead? I've posted above about that.

Yes, I have.

And your conclusions are the same that my statement : this can be very long to compute if I have many dimensions / many values.

I tried with a huge quantity of data and my conclusion is that

```sum(if(Country_dim = Country, Value))
/
count(Country_dim)

```

is faster than

```sum(if(Country_dim = Country and City_dim = City and Street_dim = Street, Value))
```

That's weird...