topic Re: Feature or bug? in QlikView

Feature or bug?

johanfo2 — Mon, 10 Sep 2012 12:33:15 GMT

I'm struggeling to understand what I'm doing wrong here.

I have a table with Brokers and prices (and more columns, but that is not of relevance). When I set up a chart table to count how many rows that have Price = 0 per broker, I have tried the following expressions:

=sum(if(Price=0, 1,0)) <--- Does work

=count({1<Price={"<0.00001"}>} Broker) <-- Does work

=count({1<Price={0}>} Broker) <---- Doesn't work

What I find amazing is that when I use set analysis, I can't compare with 0, even though the data in the table is 0. I have to compare it with extremly small number. When I use the fist expression listed above, I can successfully compare it with zero.

Why?

Re: Feature or bug?

Miguel_Angel_Baeyens — Mon, 10 Sep 2012 12:46:08 GMT

Hi Johan,

What version of QlikView are you running? It does not seem as an expected behavior, but it would be very useful if you could upload a sample set of data so we can check further. Are they null values or empty values or actual 0 values?

Hope that helps.

Miguel

Re: Feature or bug?

johanfo2 — Mon, 10 Sep 2012 12:52:29 GMT

I run QV 10 SR5. I will try to upload a sample!

Re: Feature or bug?

johanfo2 — Mon, 10 Sep 2012 13:08:37 GMT

Here is the upload example.

Re: Feature or bug?

flipside — Mon, 10 Sep 2012 13:43:38 GMT

=count({1<Price={0.00}>} Broker)

The above seems to work, but only with 2 decimal places.

flipside

Re: Feature or bug?

johanfo2 — Mon, 10 Sep 2012 13:45:54 GMT

Amazing! I thought I had tried all possible variations, except that! This must be a serious bug? Howcome it remains in SR5?

Update: I tried this trick (0.00) in my main document, however it didn't work there!! The whole thing seems unreliable to the bone.

Re: Feature or bug?

whiteline — Mon, 10 Sep 2012 13:48:49 GMT

Cool...

You can change number format for 'Price' instead of set to get everithing work.

Re: Feature or bug?

rwunderlich — Mon, 10 Sep 2012 16:48:00 GMT

Let's call it a Feature. The value inside {} is a search expression, and follows the rules for search expressions. A value by itself in a search expression indicates a string search, therefore you will only match on the exact string "0". Not "0.0" or "0.00" etc.

So figure out the correct syntax for numeric search by practicing in a listbox. The search in a listbox would be:

=Price=0

So your correct set expression is:

=count({1<Price={"=Price=0"}>}Broker)

Easy, right?

-Rob

http://robwunderlich.com

Re: Feature or bug?

johanfo2 — Mon, 10 Sep 2012 17:23:24 GMT

Thanks a lot for your answer.

I have to say that while I really like Qlikview, and haven't been close to finding any other software which has its responsivness and features, certain elements of qlikview are just plain insane!

Re: Feature or bug?

flipside — Mon, 10 Sep 2012 18:41:17 GMT

Investigating a bit more, because I agree this is a dodgy feature to me, it looks like Qlikview treats a field's zero values based on the format of the first zero value it finds in that field. So ...

A, 0.001

B, 1.001

C, 0.00

C, 0.000

C, 0

... means you can match Price = {0.00} and it will count all values of C the same. If you had C, 0.000 as the first zero value, you have to match Price = {0.000}.

If you go to Document Properties > Number, you can override this by selecting the format specified, say set everything to 3 decimal places, then match Price = {0.000} regardless of the order of zeroes.

Re: Feature or bug?

rwunderlich — Mon, 10 Sep 2012 19:21:58 GMT

It's not just zero values, it's the first string representation loaded. This is a standard feature of QV. So:

001

00001

will all display as string "001".

-Rob

Re: Feature or bug?

flipside — Mon, 10 Sep 2012 19:58:54 GMT

In this case, the first Price value loaded was 0.001 so why does the zero value format to 0.00 and not 0.000?

Re: Feature or bug?

rwunderlich — Mon, 10 Sep 2012 20:14:01 GMT

I wasn't clear. The first string representation for each equivalent numeric value is used.

-Rob

Re: Feature or bug?

flipside — Mon, 10 Sep 2012 20:24:59 GMT

Thanks Rob, I had a feeling that was what you meant!

Re: Feature or bug?

Tue, 11 Sep 2012 09:09:45 GMT

Hi Guys,

I found this text from QV11 tutorial, and after going through this discussion i am bit confused whether 0,0.001,0.1 for all which numeric equivalent is 0.

Data storage inside QlikView

In order to understand data interpretation and number formatting in

QlikView, it is necessary to know how data is stored internally by the program.

All of the data loaded into QlikView is stored in two representations,

as a string and as a number.

1 The string representation is always available and is what is shown in

the list boxes and the other sheet objects. Formatting of data in list

boxes (number format) only affects the string representation.

2 The number representation is only available when the data can be

interpreted as a valid number. The number representation is used for

all numeric calculations and for numeric sorting.

If several data items read into one field have the same number representation,

they will all be treated as the same value and will all share the first

string representation encountered. Example: The numbers 1.0, 1 and 1.000

read in that order will all have the number representation 1 and the initial

string representation 1.0.

Can someone clear this with a sample.

Regards,

Sri

Re: Feature or bug?

whiteline — Tue, 11 Sep 2012 09:26:09 GMT

Hi, srikarch.

Just re-read the answer marked as right.