topic Exclude values from count with set analysis in QlikView

Exclude values from count with set analysis

Fri, 17 Apr 2015 06:26:17 GMT

Hello everyone,

I have a some id numbers that I want to count to do something with that value.

But I do not want to count every values, only the ones, that the third digit is different from '7'.

For example:

I have these ids:

0570035

0530065

0550099

0560016

0510015

0570010

0520040

0580002

With a normal count -> count(ID) I get 8

But I want to exclude 0570035 and 0570010, so that I have a count of 6.

For this I am making the following set analysis -> count({ID<mid(ID,3,1)>}ID).

And it is not working because I do not know how to integrate a function like the MID with the set analysis.

Can anyone help?

Best regards.

Ivo

Re: Exclude values from count with set analysis

datanibbler — Fri, 17 Apr 2015 06:39:40 GMT

Hi Ivo,

why so complicated?

For a normal COUNT, I think you do not need Set_analysis.

You can use the Index function to find out whether the digit '7' is somewhere in your field (Index() will return 0 if the digit was not found)

Try like

>> COUNT(IF(Index([ID], '7')=0, ID)) <<

HTH

P.S.: Of course you can also modify the Index() function to look only at the 3rd digit - in case the character '7' is there, INDEX([ID], '7') will return the number 3.

Re: Exclude values from count with set analysis

Anonymous — Fri, 17 Apr 2015 06:48:11 GMT

if you want to use set analysis for the same

you need to create a flag in script

mid(ID,3,1) as flag

the in expression use

=count({<flag-={7}>}ID)

Thanks

BKC

Re: Exclude values from count with set analysis

brunobertels — Fri, 17 Apr 2015 06:50:12 GMT

Hi Ivo

The solution with INDEX is a good way

Another one could be this one :

Count({$<0570035=,0570010=>}ID)

( should count ID removing value 050035 and 050010)

This one should works also :

Count({$<ID-={0570035,0570010}>}ID)

( count ID except ("-=") 0570035,0570010)

Hope it will help

Regards

Bruno

PS : I personnaly use this link to build ma set analisys, provided by Stefan WALTHER

Set Analysis Wizard for QlikView | qlikblog.at

Re: Exclude values from count with set analysis

Marcellino_Groothof — Fri, 17 Apr 2015 06:52:05 GMT

Hi,

Try this, maybe you can use it,

Greetings Marcellino

Data:

LOAD * inline
[Code, excl,
0570035,35
0530065,65
0550099,99
0560016,16
0510015,15
0570010,10
0520040,40
0580002,02
];

Count ({$<excl -={35,15}>} Code)

Re: Exclude values from count with set analysis

jagan — Fri, 17 Apr 2015 07:05:37 GMT

Hi,

The best option is create a flag in script and use that in Set analysis simply like below

LOAD

If(Mid(ID,3,1) <> '7', 1, 0) as Flag

FROM DataSource;

Now in front end you can simply use

Sum(Flag)

Count({<Flag={1}>} ID)

If you don't want to change the script then use below expression

Sum(Aggr(If(Mid(ID,3,1) <> '7', 1, 0), ID))

Regards,

Jagan.

Re: Exclude values from count with set analysis

Fri, 17 Apr 2015 07:38:32 GMT

Hi DataNibbler,

I was making some test with the index function and ended up not using it, but I used the structure you suggested.

I ended up with: count(if(mid(ID,3,1)<>7,ID))

And it works nicely.

Thank you for the help!