3 Replies Latest reply: Jun 2, 2012 8:07 AM by Stefan Wühl RSS

    Need help with Count Match agg exp

      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
          Stefan Wühl

          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

              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
                  Stefan Wühl

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