Forum Discussion

JenniferJuhasz's avatar
JenniferJuhasz
Qrew Trainee
3 years ago

Query to Count the # of Unique Instances

Hi - 
I posted this 13 days ago, but onto an older thread that seemed relevant - but never received a reply, so I'm thinking it just got lost in the shuffle...

I'm trying this code below to count the Number of unique communities that attend an event, but I'm struggling to get the query correct.  So - as an example, say we hold an event, and have 50 attendees, representing 10 different communities - how can I get that unique count of 10?  My code keeps returning a syntax error and I'm completely puzzled as to why.  

My Formula Query is:
var text QUERY = "{7.OAF.'" & [Month] & "'} AND {7.OBF.'" & LastDayOfMonth([Month]) & "'}";
GetFieldValues(GetRecords($QUERY,"dbID") & ("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 do this?

Thank you in advance for your help and time! 

Jen

​

------------------------------
Jennifer Juhasz
------------------------------
formula queries

1 Reply

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    The Formula query will have to identify records which are the first attendee in each group and then count with them with the Size.  If you want some one on one assistance to get this working please contact me by email from my signature line. The syntax is quite tricky for formula queries so there may not be many of us on this forum who can just wing it and get the syntax correct.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------