Exclude values from count with set analysis

[#1]

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

[datanibbler]

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.

[Anonymous]

hi

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

[brunobertels]

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

[Marcellino_Groothof]

  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)

[jagan]

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)

OR

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.

[#7 · Accepted solution]

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!