Need help with Count Match agg exp

[#1]

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

[swuehl]

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?

[#3]

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

[swuehl]

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.]))