Tagging Customers on Services

Options

Hi Community, excited to see what you think up!

BLUF: How can I analyze what nurse saw a patient prior to a surgery or a non-surgery?

See my simplified dataset below. My hope is to be able to see which nurses saw which patients/customers that led to a surgery (or not).

  1. My current thought is a CASE WHEN to create a label for "surgical patients", but this doesn't capture pre-operative testing and evaluations. For instance:

CASE

WHEN Treatment = 'Surgery' THEN 'Surgical Patient'

ELSE 'Non-surgical Patient'

This logic missed the Consult, the testing, the evals, etc. that lead up to the surgery.

2. If I take all Patients that had a surgery and then do a large "CASE WHEN" on them, then I get duplicates downstream, which also isn't ideal. For instance:

CASE

WHEN Patient = 1, 2, OR 4 THEN 'Surgical Patient'

ELSE 'Non surgical patient'

This fails because then it shows FIVE "Surgical Patients" for Patient 1, even though only 1 surgery was performed.

I feel as though I'm very close but just need someone to get me across the finish line 😊🏁🏁

Best Answers

  • ColemenWilson
    edited June 18 Answer ✓
    Options

    Here is the solution to your BLUF:
    1. Use Group By tile in Magic ETL and the following formula: GROUP_CONCAT(DISTINCT CASE WHEN Treatment IN('Consult','Testing','Evals','Preop') THEN Name END)

    You don't have a nurse identifier in your sample data so I added one called "Name"

    Results:

    This will show which nurses saw each patient. Let me know if this isn't what you're looking for.

    For the patient count piece, you need to use COUNT(DISTINCT ) with a CASE statement:

    COUNT(DISTINCT CASE WHEN Treatment IN('Consult','Testing','Evals','Preop') THEN Patient END)

    For analyzing which nurses saw patients that led to surgery vs. no surgery, you could add a flag to every patient that led to surgery in Magic ETL as shown below:

    Then create some kind of card in analyzer using this beastmode:
    COUNT(DISTINCT CASE WHEN Had Surgery = 'true' THEN Patient END) / COUNT(DISTINCT Patient)

    Result:

    75% of patients John saw ended up in surgery, 67% of patients Jacob saw ended up in surgery.

    If I solved your problem, please select "yes" above

  • Data_Devon
    Data_Devon Member
    Answer ✓
    Options

    Wow!! Thanks a ton for all that. I think we are super close but I must've missed something somewhere.

    You can see my raw data at far left. Then, I did a "Group By" per your instruction. The output was all the non-surgical appointments. I then joined this back to the original dataset because I will need the actually surgeries so I can compare to the whole.

    Along the bottom line of the ETL, I filtered down to "Surgical Patients" as you instructed, added the constants, and then joined it back up with the original dataset.

    Finally, I added the IFNULL()

    Unfortunately, there were duplicates created somewhere in my joins, so the output dataset no longer matches the original dataset. I.e.:

    Count(Surgeries) != Count(Surgeries)

    Any ideas??

Answers

  • ColemenWilson
    edited June 18 Answer ✓
    Options

    Here is the solution to your BLUF:
    1. Use Group By tile in Magic ETL and the following formula: GROUP_CONCAT(DISTINCT CASE WHEN Treatment IN('Consult','Testing','Evals','Preop') THEN Name END)

    You don't have a nurse identifier in your sample data so I added one called "Name"

    Results:

    This will show which nurses saw each patient. Let me know if this isn't what you're looking for.

    For the patient count piece, you need to use COUNT(DISTINCT ) with a CASE statement:

    COUNT(DISTINCT CASE WHEN Treatment IN('Consult','Testing','Evals','Preop') THEN Patient END)

    For analyzing which nurses saw patients that led to surgery vs. no surgery, you could add a flag to every patient that led to surgery in Magic ETL as shown below:

    Then create some kind of card in analyzer using this beastmode:
    COUNT(DISTINCT CASE WHEN Had Surgery = 'true' THEN Patient END) / COUNT(DISTINCT Patient)

    Result:

    75% of patients John saw ended up in surgery, 67% of patients Jacob saw ended up in surgery.

    If I solved your problem, please select "yes" above

  • Data_Devon
    Data_Devon Member
    Answer ✓
    Options

    Wow!! Thanks a ton for all that. I think we are super close but I must've missed something somewhere.

    You can see my raw data at far left. Then, I did a "Group By" per your instruction. The output was all the non-surgical appointments. I then joined this back to the original dataset because I will need the actually surgeries so I can compare to the whole.

    Along the bottom line of the ETL, I filtered down to "Surgical Patients" as you instructed, added the constants, and then joined it back up with the original dataset.

    Finally, I added the IFNULL()

    Unfortunately, there were duplicates created somewhere in my joins, so the output dataset no longer matches the original dataset. I.e.:

    Count(Surgeries) != Count(Surgeries)

    Any ideas??

  • Data_Devon
    Options

    I just had to add additional join keys on the final join to alleviate the duplications that were happening.

    This is good to go! Thanks a ton @ColemenWilson for your help.