Forum Discussion

LashellThomas's avatar
LashellThomas
Qrew Member
2 years ago

Distinct Count on a combined text field

I need to identify the number of unique inpatient admissions from claims data.  Each inpatient stay can generate multiple claims each with a unique claim identifier.  The claims would all share the same admit start date. the claim also has the member id of the member who had the inpatient stay.

I want to count the number of inpatient visits visit for each member regardless of how many claims were submitted for the inpatient visit.

I have attempted to do combine the claim ID with the admit date and want to do a distinct count of that....

------------------------------
Lashell Thomas
------------------------------
Formulas and functions
Relationships

2 Replies

Sort By
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    2 years ago
    Thanks to @Mark Shnier (Your Quickbase Coach) for this bit of code to count combine text fields:

    var text value = ToText([Combined Text Field]);

    Count(

    Trim(Part($value,1,";")),
    Trim(Part($value,2,";")),
    Trim(Part($value,3,";")),
    Trim(Part($value,4,";")),
    Trim(Part($value,5,";")),
    Trim(Part($value,6,";")),
    Trim(Part($value,7,";")),
    Trim(Part($value,8,";")),
    Trim(Part($value,9,";")),
    Trim(Part($value,10,";")),
    Trim(Part($value,11,";")),
    Trim(Part($value,12,";")),
    Trim(Part($value,13,";")),
    Trim(Part($value,14,";")),
    Trim(Part($value,15,";")),
    Trim(Part($value,16,";")),
    Trim(Part($value,17,";")),
    Trim(Part($value,18,";")),
    Trim(Part($value,19,";")),
    Trim(Part($value,20,";"))
    )
    ​

    ------------------------------
    Mike Tamoush
    ------------------------------
  • DougHenning1's avatar
    DougHenning1
    Community Manager
    2 years ago
    If you want the count of distinct admit dates for each member, you can use a summary report to get this info.  Sample claims data with unique claim ids:

    Summary report counting each unique admit date for each member:

    Summary report settings, grouping by member id and summarizing on distinct count of admit date:

    Hope that helps!


    ------------------------------
    Doug Henning
    ------------------------------