topic Re: Decimal numbers in set analysis in QlikView

Decimal numbers in set analysis

Fri, 08 Apr 2016 19:26:38 GMT

Hi,

I'm trying to figure out how to get all rows with zero values using set analysis. Say I have a table:

ASD:

LOAD * INLINE [

Id, Quantity

1, 0.0000

2, 0.0000

3, 0.0002

4, 1.0002

5, 1.0000

];

This expression: sum({<Quantity = {0}>} 1) will not return any rows. However, this will: sum({<Quantity = {'0.0000'}>} 1).

So apparently QV treats the field as a string in the set analysis. In all other calculations the field is treated as a number (for example IF(Quantity = 0, 1) will work. How can I get the first expression to work, or what would be the best alternate solution? I'm working with large tables so I'm trying to find the solution with the best performance.

Thanks,

Steve

Re: Decimal numbers in set analysis

sunny_talwar — Fri, 08 Apr 2016 20:43:41 GMT

As its current form, it seems to be read as string, try this:

ASD:

LOAD Id,

Num(Quantity) as Quantity;

LOAD * INLINE [

Id, Quantity

1, 0.0000

2, 0.0000

3, 0.0002

4, 1.0002

5, 1.0000

];

Re: Decimal numbers in set analysis

alexpanjhc — Fri, 08 Apr 2016 20:46:57 GMT

to prove your theory I used the following set analysis to get 0.0002

sum({<Id={'3'}>} Quantity) + sum({<Id={'1'}>} Quantity)

so the quality as treated as numbers. I am not sure what your question is.

Re: Decimal numbers in set analysis

Fri, 08 Apr 2016 20:52:32 GMT

Steve, in SET Analysis set modifiers always expect same format of the modifier field. Good Example is Date or Time stamp field modifiers

So I am not surprised why set analysis is not working in your case sum({<Quantity = {'0.0000'}>} 1) vs sum({<Quantity = {0}>} 1) . I would suggest add the flag field to satisfy your condition and use the flag in the Set Modifier.

Re: Decimal numbers in set analysis

johnw — Fri, 08 Apr 2016 23:05:02 GMT

Put another way, set analysis does field comparison as a text comparison, even on numeric/date fields.

I normally would just use Quantity={'0.0000'}. But I agree with using a flag for maximum performance. As I recall, fastest is true()/null() as your true/false values, then QuantityZeroFlag={'-1'}, since true()=-1. I remember there being speed differences between QlikView versions, though, and it's been years since I've tested the speed of different flag options myself.

Re: Decimal numbers in set analysis

Sat, 09 Apr 2016 00:39:55 GMT

Steve, you can also use SET analysis for this. Please find the below:

=Count({<Quantity={"=Num(Quantity)=0"}>}Id)

If you can write 0.* in SET analysis, but it will count Quantity starting with 0.

=Count({<Quantity={"0.*"}>}Id)

Re: Decimal numbers in set analysis

swuehl — Sat, 09 Apr 2016 11:54:42 GMT

As others already mentioned, that's the way QV is currently doing field value comparison in set analysis.

If you follow the discussions in the forum, you'll see that this is probably #1 of user issues with set analysis

(i.e. struggling to get the field modifier element list matching with field values, e.g. on dates / timestamps.

Re: Decimal numbers in set analysis

Sat, 09 Apr 2016 12:22:40 GMT

Thanks everyone,

I'm indeed only interested to pick the rows where quantity is exactly zero, and I ended up using a flag, which works (but not the prettiest solution for me, since I actually have multiple Quantity-columns for which I have to make a flag for each, but that's another matter)