Decimal numbers in set analysis

[#1]

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

[sunny_talwar]

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

];

[alexpanjhc]

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.

[#4]

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.

[johnw]

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.

[#6]

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)

[swuehl · Accepted solution]

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.

See also: Dates in Set Analysis

)

I really hope Qlik will simplify this part someday soon, at least HIC agreed there's a need to work on that in another discussion. I believe he can drive things into this direction.

As others already mentioned, you can create a flag in the script or use an advanced search in the field modifier (you don't need num() here, so =Count({<Quantity={"=Quantity=0"}>} Id) should do).

If you are specifically interested in the zero Quantity values, a flag is probably the best solution.

[#8]

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)