Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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.]))