topic Need help with Count Match agg exp in QlikView

Need help with Count Match agg exp

Fri, 01 Jun 2012 21:44:06 GMT

Hello,

I loaded two tables where some data match in between. I want to count if certain criteria is met.

Ex:

Table1

Com No. Beb No.

1 A

2 B

3 C

Table2

Com No2 Beb no.

4 Z

2 B

5 Y

I need to count how many Com No. = Com No.2 where Beb No. = 'B'

I tried this but didn't work: Count( {$ < [Beb No.] = {'B'} , [Com no.] = [Com no.2] > } [Com No.])

of course the count should be = 1

any Ideas !!

BR,

Walli

Need help with Count Match agg exp

swuehl — Fri, 01 Jun 2012 22:48:59 GMT

Not sure if that's what you're after, but maybe

=sum({$<[Beb No.]={B}>} if([Com No]=[Com No2], 1))

do you have a 1:1 or n:m relationship between the to tables (can there be multiple occurences of B in each table, with different Com No/ Com No2 values related? If so, what's your expected outcome for such a setting?

Need help with Count Match agg exp

Sat, 02 Jun 2012 09:50:18 GMT

yes the above was only an micro example of big tables where B is a category or a type of sale and com no is the product number.

what I expect is to count how many times we have the same product number under B category, under c category ,... so on (from both tables.)

your formual kindda worked, but taking in consideration that I have n:m relation how can I also take out doublication with Distinct Com no.

thanks a lot

Need help with Count Match agg exp

swuehl — Sat, 02 Jun 2012 12:07:26 GMT

In the script, you could use additional tables with distinct loaded fields.

If you need to do it in the front end, you may try

=sum( aggr(if(sum( if([Com No]=[Com No2], 1))>0,1),[Beb No.]))

or if you want to filter by [Beb No.]

=sum({<[Beb No.]={B}>}aggr(if(sum( if([Com No]=[Com No2], 1))>0,1),[Beb No.]))