Help with 2 Query Report

[jsisock79]

Hi. I’m trying to write a report where I am using two queries. The first query captures basic information such as the MRN and name. The second query captures the admit dates, MRNs, and their diagnosis for each admit date. I am trying to bring the most recent diagnosis over to the first query using the max admit date for each patient using the merged MRN. I am able to get the max admit date for each patient, but it is not bringing over any data when I try to bring the diagnosis over to the main tab. Below are my two formulas I put together. The max admit formula seems to work, but the max diagnosis formula brings over blank rows. Thanks in advance for any help you can provide.

Query 1

Person Name	MRN	Age	Max Diagnosis
Mike	98	18
Tom	32	18
Steve	23	20
Sal	21	19
Fred	103	18

Query 2

Variables

Max Admit Date
=Max([Admit Date & Time]) In ([MRN])

Max Diagnosis
=([Diagnosis Description]) Where ([Max Admit Date]=[Admit Date & Time]) In ([MRN])

[nscheaffer]

Try changing your Max Diagnosis variable formula to…

=Max([Diagnosis Description]) Where ([Max Admit Date]=[Admit Date & Time]) ForEach([MRN])

Does that work for you?

[jsisock79]

I was hoping that would work, but it’s giving me a #CONTEXT error.

[nscheaffer]

Can you provide some sample data we can work with?

Use Free-Hand SQL to Increase the Likelihood of Getting an Answer

I post this over on the SAP Community site a few days ago. I thought it might be helpful to have here as well as a reference. We have all seen questions with sample data provided as text like this… ID | Some Description | Some Date | Some Number 1 | Whatever… | 1/15/2024 | 150 2 | Something else… | 1/31/2024 | 200 3 | Still more… | 2/1/2024 | 500 Or in a table… ID Some Description Some Date Some Number 1 Whatever… 1/15/2024 150 2 Something else… 1/31/2024 200 3 Still more… 2/1…

[jsisock79]

Sure thing. I used the CSV to SQL converter below for both sets of data. I hope that is helpful. Thank you again for helping me.

CREATE VIEW mytable(“Person Name”,MRN,Age,“Max Diagnosis”) AS
SELECT ‘Mike’ AS “Person Name”,98 AS MRN,18 AS Age,NULL AS “Max Diagnosis”
UNION ALL
SELECT ‘Tom’,32,18,NULL
UNION ALL
SELECT ‘Steve’,23,20,NULL
UNION ALL
SELECT ‘Sal’,21,19,NULL
UNION ALL
SELECT ‘Fred’,103,18,NULL;

CREATE VIEW mytable(“Person Name”,MRN,Age,“Max Diagnosis”) AS
SELECT ‘04/29/2024 05:23:43 AM’ AS “Person Name”,04/26/2024 01:00:00 PM AS MRN,15 AS Age,‘Scar’ AS “Max Diagnosis”
UNION ALL
SELECT ‘04/29/2024 05:23:43 AM’,04/29/2024 05:23:43 AM,15,‘Scar’
UNION ALL
SELECT ‘04/04/2024 08:00:00 AM’,04/04/2024 08:00:00 AM,103,‘Complication’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,04/03/2024 10:00:00 AM,51,‘Examination’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,04/04/2024 08:59:36 AM,51,‘Other’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,04/17/2024 08:56:23 AM,51,‘Hypertrophy’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,04/17/2024 12:45:00 PM,51,‘Scar’;

[nscheaffer]

You are getting close.

In the CSV to SQL Converter you can uncheck the “Create Table/View” checkbox since that is unnecessary. In the end, you just need the SELECT statements for each row of data unioned together. That is remedied easily enough on my end.

The bigger issue is that MRN is defined differently in each query (number in the first, datetime in the second) and that is what I understand you want to join on.

Please correct or clarify.

[jsisock79]

Thank you. Below is the updated sample data. I think it worked correctly this time.

SELECT ‘Mike’ AS “Person Name”,98 AS MRN,18 AS Age,NULL AS “Max Diagnosis”
UNION ALL
SELECT ‘Tom’,32,18,NULL
UNION ALL
SELECT ‘Steve’,23,20,NULL
UNION ALL
SELECT ‘Sal’,21,19,NULL
UNION ALL
SELECT ‘Fred’,103,18,NULL;

SELECT ‘04/29/2024 05:23:43 AM’ AS “Max Admit Date”,‘04/26/2024 01:00:00 PM’ AS “Admit Date Time”,15 AS MRN,‘Scar’ AS “Diagnosis Description”
UNION ALL
SELECT ‘04/29/2024 05:23:43 AM’,‘04/29/2024 05:23:43 AM’,15,‘Scar’
UNION ALL
SELECT ‘04/04/2024 08:00:00 AM’,‘04/04/2024 08:00:00 AM’,103,‘Complication’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,‘04/03/2024 10:00:00 AM’,51,‘Examination’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,‘04/04/2024 08:59:36 AM’,51,‘Other’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,‘04/17/2024 08:56:23 AM’,51,‘Hypertrophy’
UNION ALL
SELECT ‘04/17/2024 12:45:00 PM’,‘04/17/2024 12:45:00 PM’,51,‘Scar’;

The MRN is the same object for both queries. It should be a number. I merged on the MRN field and tried using the merged object in the formulas.

[nscheaffer]

As a side note, the platform that hosts BOB is weird in how it deals with quotes.

This is a blockquote…

SELECT ‘Hello World!’

This is preformatted text…

SELECT 'Hello World!'

When I copy and paste to SQL Server, I will get curly quotes on the blockquoted text which will not work in SQL Server. However, with the preformatted text I get straight quotes which will work in SQL Server. I wrote about this a while go here.

[nscheaffer]

The two tables on the top are the results of your sample data queries. I added another record (“Bob”) to your first query. I think I am getting the result you seek.

My formula for Var Max Diagnosis looks to be the same as yours…

=[Diagnosis Description] Where ([Max Admit Date]=[Admit Date Time]) In([MRN])

What is the qualification (Dimension, Detail, or Measure) for each of your objects? In my screenshot, you can see by the icon next to each one that they are all Dimensions.

I am not sure why you are getting a #CONTEXT message. I am not sure what to have you try.

Noel

[jsisock79]

I got lucky, because I made a change to your first variable formula and now it is working. I changed the Var Max Diagnosis to the below (changed ForEach to In) and now it is pulling the data over with no errors. The Var Max Diagnosis variable is a Measure.

=Max([Diagnosis Description]) Where ([Max Admit Date]=[Admit Date & Time]) In ([MRN])

Thank you again for all of your help!

[nscheaffer]

Someday I will be able to know (and explain) in exactly what situations to use ForEach, ForAll, and In. Today is not that day.