2 Replies Latest reply: Apr 16, 2012 6:50 AM by Anne Duffy RSS

    Count Distinct

      Hi Guys

       

      I am wring a report that compares a result from a Warehouse and one from an Excel sheet.

       

      Say my Warehouse is like this :

       

      WHCustomer              Amount

      ABC                            50

      123                             100

      Do ray me                    20

      ABC                            75

       

      And my excel is like this :

       

      Customer                   Amount

      ABC                           50

      Fred                           100

      Nuals                          50

      ABC                           100

       

      On my straight table when I write my match formula I would like a result of 1 for the first ABC.

       

      However I am getting a result of 2 using the following formula - what it is doing is using 2 calculated fields

      WHAccount is a text join of Customer and Amount from Warehouse ( I did this is script WHCustomer&Amount as WHAccount) and AccountNumber is a text join Customer and Amount from excel, the RecWh is recno() as RecWH used as an effort to give uniqueness to each row !!)

       

      However it is still just counting all Customer names where ANY account matches

       

      count(if(WHAccount=AccountNumber,RecWH))

       

      I have tried variations of "Distinct" but cant get it , can anyone please advise ?


      Thanks

      A