topic Conditions in set analysis in QlikView

Conditions in set analysis

Anonymous — Fri, 09 Dec 2011 21:49:50 GMT

Hello,

I have set analysis. I want to choose all data exept some data.I mean something like "ID < 7.77e+014 and ID > 7.99e+014".

I tryed many differnt ways

Count({$<ID={"=ID<=7.7700001678389e+014" },ID={"ID>=7.7700002244059e+014">}surveyID)-------------Nothing

Count({$<ID={"7.77*"}>}surveyID)-----------------------select exactly that I want not includ.

Count({<ID={"=ID<=7.77e+014"}>}surveyID)--------------it works but without second part of condition.

I don't know why it loads my ID in this format 7.7700002244059e+014. It should be 777000022440585.

Thanks for your help,

Vladimir

Re: Conditions in set analysis

swuehl — Fri, 09 Dec 2011 23:18:39 GMT

Yeah, that's an interesting question.

There are some, but not too many posts here in the forum which talk about problems reading in large (integer) values with more than some 14 digits.

One posted solution is to use evaluate() on the field with the large integer number. Indeed, this did the job for me with QV10 SR3, like

LOAD *, evaluate(ID) as ID2 INLINE [

ID, Value

777000022440585, 1

777000022440586, 2

12321321, 3

877000022440585, 4

7.7700002244059e+014, 5

6.7700002244059e+014, 6

8.7700002244059e+014,7

1234567890123456, 8

];

Then you could use something like

=sum({$<ID2={"=ID2<>777000022440585"} >} Value)

to filter out the value you want to exclude.

Conditions in set analysis

Anonymous — Mon, 12 Dec 2011 19:47:49 GMT

for this case: when Id <5 and Id > 7. I found out

Count({$<ID={"=ID<7.77e+014","=ID >7.7799999999999e+014"}>}ID)-----------------------works perfect.

BUT

When ID>5 and ID<7 I can't find right solution.

Maybe someone knows how to exclud 5,6,7?

Thanks,

Conditions in set analysis

swuehl — Tue, 13 Dec 2011 09:07:17 GMT

To exclude values, try

Count({$<ID -= {5,6,7}>} ID)

Please note the minus-equal sign.

Something like this should combine it with your previous expression:

Count({$<ID={"=ID<7.77e+014","=ID >7.7799999999999e+014"} - {5,6,7} >} ID)

Conditions in set analysis

Anonymous — Tue, 13 Dec 2011 16:55:17 GMT

Hi swuehl,

Count({$<ID={"=ID<7.77e+014","=ID >7.7799999999999e+014"}>}ID)------

This expressions selects like example 0,1,2,3,4 and 8,9,10,11, ...

I need expressions which select 5,6,7 only. In my case it's thousands. I can't select them one by one .

I don't understandt how to group them like from 5 to 7.

Maybe it's use to use condition IF

This my expressions

Count({$<SOURCE={'2'},TASK_COMPLETION={Yes}>} TASK_COMPLETION)/

Count({$<SOURCE={'2'}>}TOTAL <Period> TASK_COMPLETION)

Instead of SOURCE=2 should be ID from 7.77e+014 to 7.99999999e+014.

Who knows how to write expression?

Thanks,

Conditions in set analysis

swuehl — Tue, 13 Dec 2011 17:14:30 GMT

7.7799999999999e+014 is way larger than 8,9,10,11 (it is in scientific notation 7.7799 * 10^14), so I think the scientific notation don't get correctly parsed in the set element

(edit: I just tried, and it works at my place, you I don't know why you get "0,1,2,3,4 and 8,9,10,11, ..." returned. This doesn't make sense to me)

Anyway, have you had a chance to look into my above sample and have you tried using the evaluate function in your load?

I think your expression could then look similar to

count({$<ID={"=ID>777000000000000 and ID<799999999999999"} >} Value)

count({$<ID={"=ID>7.77e14 and ID<7.99999999e+014"} >} Value)

Regards,

Stefan

Conditions in set analysis

Anonymous — Wed, 14 Dec 2011 18:09:23 GMT

Thanks Stefan,

You are right.

Count({$<ID={"=ID>=7.77e+014 AND ID <=7.7799999999999e+014"}>}ID)

It works !!!!!!!!!