topic Re: [SR10] Questions / remarks / bug on Set Analysis in QlikView

[SR10] Questions / remarks / bug on Set Analysis

nstefaniuk — Tue, 10 Jan 2012 10:47:22 GMT

Hello everybody.

I have some questions / remarks (maybe bug) on Set Analysis.

1) Empty Set

According to documentation, the empty set ( field = {} ) should select the data for which no value is defined.

I have tried to apply it on null but without success. I know how to select null values, but does someone have an example of empty set use ?

2) Use tilde for minus operande

According to documentation, ~field = {....} should be equal to field -= {....} and field = field - {....}

But the whole set analysis is ignored if I try to use it.

The documentation says "Finally, for fields in and-mode, there is also the possibility of forced exclusion. If you want to force exclusion of specific field values, you will need to use “~” in front of the field name.", but what is "and-mode" ?

3) Use # in Set Analysis

What is the difference between

field = {$(vChoice)}

and

field = {$(#vChoice)}

Can we use # for a specific syntax / fonctionnality ?

4) Strange behaviour in sub set analysis

This part is more complex than the others. I have 2 unexpected behaviours with Set Analysis.

First is an issue with the empty set (again). If I write

sum({$<id={">=2<=1"}>}value)

I guess that I will have no row, but I have all the rows, as if the whole Set Analysis was ignored.

If I write my set like that

sum({$<id={">=2"}*{"<=1"}>}value)

it works

Second issue is very complex. It seems that, in a specific case, when there is a sub set, the $ sign is not considered and the sub set works as if there is a 1 in the set. The detail is in the attached application.

Thanks a lot for your help

Re: [SR10] Questions / remarks / bug on Set Analysis

Miguel_Angel_Baeyens — Tue, 10 Jan 2012 12:15:16 GMT

Hi,

Unfortunately, I cannot open your application right now, but anyway I'd try to answer your questions:

1.- Never used it. Probably the QlikTech documentation guys need to elaborate the example.

2.- "And" fields are documented, and very unsual as far as I'm concerned. Check Reference Manual, Book III, Appendix, D.4 "QlikView Logic" on how to build these fields and what they are for. In any case, ~Field1 is not equal to Field2 -= {Value}. Field1 is an AND MODE field.

3.- The pound sign forces to return a numeric value for the expression evaluated, if possible. If the variable already does that, you needn't specify the pound. Think of dates (dates are dual in QlikView, and although they are displayed as a literal, they have a numeric value):

$(=Date('31/12/2012'))

will return 31 divided into 12 divided into 2012 (so 0,00000... something), because the expansion returns a evaluable expression (31/12/2012). If you do want the numeric value of the date then you need to specify it, because you don't want to evaluate the result, just the value of the Date() function:

$(#=Date('31/12/2012'))

will return 41274 which is a correct numeric value for that date in QlikView.

But $(=2010) and $(#=2010) will return the numeric value 2010, so the "#" has no effect.

4.- It may sound strange, but there are no records between 2 and 1 in QlikView. QlikView does not go backwards (in that case, that would make sense). That's why you need to use two set modifiers instead of one. But there may be records between 1 and 2 (called a range) and in this case you don't need to add another set modifier. Indeed, the set analysis is ignored because the selection returned by the set is not possible. Think of a field that stores values 1, 2, 3, 4 and 5.

>4<2

Will return 1 and 5, but

>5<1

Will return all possible values (as if you did no selection or >A<B, for example) because no value is possible between 5 and 1. Try these selections using a simple inline table and a listbox.

Hope all this that makes sense.

Miguel

[SR10] Questions / remarks / bug on Set Analysis

nstefaniuk — Tue, 10 Jan 2012 13:00:51 GMT

Ok thanks for the 3 first points.

Regarding the fourth, as I have guessed:

if the set analysis is a range (eg (>1<4) )

if it's a valid range, where min < max (eg (>1<4) )

-> it select the values in the range, and no value if there is no values in this range

else if the range is not valid, for example min > max (eg (<1>4) )

each modifier is avaluated separately and if there is values < min or > max

-> it select the values that answer each modifier separately. So (<1>4) is equivalent to (">1")+("<4")

else

-> all possible values are returned

end if

I am a bit shocked...

If you can open my application for the last case, I think there is a strange behaviour inside too.

Thanks a lot anyway, you are a bible

Re: [SR10] Questions / remarks / bug on Set Analysis

Anonymous — Mon, 30 Jun 2014 06:08:50 GMT

Hi,

Please elaborate more on the following points that you have explained already,I found it little bit confusing

>4<2

Will return 1 and 5, but

>5<1

Will return all possible values (as if you did no selection or >A<B, for example) because no value is possible between 5 and 1.

Thanks