Forum Discussion

PaulPeterson1's avatar
PaulPeterson1
Qrew Assistant Captain
4 years ago

Query for unique count?

As you can tell from my recent posts, I'm going down the formula query rabbit hole.  I would like a suggestion for getting a unique count for customers who subscribe to a specific service.  I have the total count of sites calculated in this post:

Formula Query - Size ?

I know I will need the Size function and can get the total sites.  I'm just not sure how to only return the unique customers.

------------------------------
Paul Peterson
------------------------------
formula queries
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    Here is something to build on

    This formula as a multi select text will return the all the Record IDs for the record where fid 73 in the while tale matches the value in a field called NPI.  In this case that was the identifier of the Customer.

    GetFieldValues(
    GetRecords("{73.EX." & [NPI] & "}"),3)

    From the inside out that formula says query for fid 73 (which is the field for NPI) and go off and get the records from the table I'm sitting on where the [NPI] for the record I'm sitting on matches with the same value in any other record. Then bring back the Field values from that Query for Field ID number 3. Of course field ID number three is the Record ID.

    So this will return as string like 123 ;  234 ; 5678

    So those three Record ID's are for the same Customer. 

    But then how to find the one with the Minimum Record ID# of all the duplicates?

    Most conveniently in my use case, the records are returned in Record ID sequence!

    This formula will check if the record I am on is the first one on the list.

    ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]

    So if you limit your Size to the ones that are the first of the duplicates your would get a count of the unique Customers.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • JenniferJuhasz's avatar
      JenniferJuhasz
      Qrew Cadet
      3 years ago
      Hi Mark,

      I'm trying this code in my own instance here, but I'm struggling to get the query correct.  Not sure what the syntax error is.

      My Formula Query is:
      var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
      GetFieldValues(GetRecords($QUERY,"bru6bq226") & ("14.EX." & [What community do you live in?] & "),12) &
      ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]

      But, the field value [What community do you live in?] is generating a syntax error.

      Oddly, it goes away when I have a repeat line below like this:
      var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
      GetFieldValues(GetRecords($QUERY,"bru6bq226") & ("14.EX." & [What community do you live in?] & "),12) &
      ToNumber(Trim(Left([Record IDs for this NPI],";"))) = [Record ID#]
      GetRecords("{14.EX." & [What community do you live in?] & "}"),12)

      But then the syntax error moves to the '.' following EX.

      I think I just have it mixed up where I'm putting in the "'s ...but not quite sure.  

      Can you offer any guidance how to get that to pull the information from the outside table?

      Thank you in advance for your help and time! 

      Jen

      ------------------------------
      Jennifer Juhasz
      ------------------------------