nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Joining two JMP Tables stored in local folder based on criteria?

vinods20150

Hello All,

I am relatively new to JMP and still exploring..

I have two jmp files in my local folder, that hold hospital patient ID and their visit date information in columns. One jmp file contains Outpatient visit data and another file inpatient visit data, both for a period from 2010 to 2015.

I want to compare and join the two jmp files such that the join provides me a subset of patients whose outpatient visit date is less than equal to 14 days from inpatient visit date (Criteria)

I am using JMP 12.0 and the The "Join" under Tables menu does not allow me to construct the criteria between the columns in the two tables.

How do I go about? Do I have to write a SQL? If so what and where should I write the SQL that can compare the two jmp tables and join them to derive the answer?

Thanks for any help.

Regards,

Vinod

Accepted answers

vinods20150

Good Morning and Thank You Ian. I ran the jsl file and the output works right..

I tried to modify the jsl to apply on the two jmp tables I had. I seem to be doing something wrong in the script as nothing happens on running script nor do I get any error message, I am attaching the edited jsl for your verification and guidance.

The above script works for scenario1, for scenario 2 that I outlined earlier where the inpatient and outpatient jmp tables are concatenated into one integrated jmp table how should the script change? To distinguish which rows of data are inpatient and which are outpatient, I had a column introduced with the flag "OP" and "IP" against each row of data. The Patient ID and Visit data of both inpatient and outpatient tables got concatenated in an patient ID and Visit Date column within the integrated jmp table.

TrialScript.jsl

All comments

ian_jmp

Given that your starting point appears to be two JMP tables, and so long as you are not short of resources, you may be as well to do the join and the application of your criterion in JMP. See the code below, but note the order of the arguments in the 'DateDifference()' function! If you want to work interactively, just look at the formula in the "Criterion Met?" column and build the analogous one for your case.

NamesDefaultToHere(1);

// Make some simulated 'date' data

nr = 100;

dt1 = NewTable("In",

NewColumn("ID", Numeric, Nominal, Values((1::nr)`)),

NewColumn("In Date", Numeric, Continuous,

Formula(RandomInteger(DateDMY(1,1,2014), DateDMY(31,12,2014))),

Format( "ddMonyyyy h:m:s", 22, 0 ),

Input Format( "ddMonyyyy h:m:s", 0 ),

)

);

inVals = Column(dt1, "In Date") << getValues;

dayDelay = J(nr, 1, Round(RandomNormal(14, 7), 0));

dayDelay[Loc(dayDelay <= 0)] = 0;

outVals = inVals + InDays(dayDelay);

dt2 = NewTable("Out",

NewColumn("ID", Numeric, Nominal, Values((1::nr)`)),

NewColumn("Out Date", Numeric, Continuous,

Values(outVals),

Format( "ddMonyyyy h:m:s", 22, 0 ),

Input Format( "ddMonyyyy h:m:s", 0 ),

)

);

// Join dt1 and dt2

Wait(3);

dt3 = dt2 << Join(

With( dt1 ),

SelectWith( :ID, :In Date ),

Select( :Out Date ),

By Matching Columns( :ID = :ID ),

Drop multiples( 0, 0 ),

Include Nonmatches( 0, 0 ),

Preserve main table order( 1 ),

Output Table( "In and Out Dates" )

);

// Add a column to indicate if the crirerion is satisfied

dt3 << New Column( "Criterion Met?",

Numeric,

"Nominal",

Format( "Best", 12 ),

Formula( If( DateDifference( :In Date, :Out Date, "Day" ) <= 14, 1, 0) ),

Value Labels( {0 = "No", 1 = "Yes"} ),

Set Selected

);

vinods20150

Hi Ian, Firstly would like to thank you for your reply and help extended.

Being new to JMP and also a non-coder, more a business guy, I need a bit more layman and step-wise guidance. I would like to go with the interactive approach as could be the most comfortable and enable faster resolution.

But before that, I would like to give you a detailed clarity of my need...I have illustrated a dummy snapshot of the data in the two tables for the purpose and listed two scenarios..followed by a 3rd related execution based clarification. Sorry for this long winding reply, but I think its necessary to be able to help provide the appropriate guidance. Thank you once again for your patience and time.

1. Scenario 1

Outpatient JMP Table 1: ( only relevant columns shown)

Year Patient ID Visit Date ..... ........ ...... ........

2010 1 03/20/2010

2010 1 05/15/2010

2011 1 01/19/2011

2011 1 08/04/2011

2010 2 06/18/2010

2010 2 07/12/2010

2010 2 08/1/2010

and so on for n patients..There can be a patient with just one outpatient visit in 5 years..

Inpatient JMP Table 2: ( only relevant columns)

Year Patient ID Visit Date ..... ........ ...... ........

2010 1 01/08/2010

2010 1 06/10/2010

2010 1 10/19/2010

2011 1 07/15/2011

2010 3 04/12/2010

2010 3 09/20/2010

2010 3 12/10/2010

and so on for n patients..There can be a patient with just one inpatient visit in 5 years. There is no data for patient 2 in this table...

What I require is given the table and data structure for each patient in outpatient table, would want to compare the outpatient visit dates and inpatient visit dates for that patient and create a subset table which records the affected outpatient row (all the outpatient table columns for that identified row) whenever the following criteria gets met: outpatient visit date is <=14 days from an inpatient visit date (Note inpatient visit date should be before the outpatient visit date before criteria is checked). This outpatient row should also include the inpatient visit date column in the subset table so created.

2. Scenario 2.

I also have a concatenated table built by concatenating the above inpatient and outpatient table and data. In this concat table, I introduced a column to identify which row of patient data was Inpatient and which outpatient and also maintained the same column names. In such a scenario, how would the formula in scenario 1 get modified to generate the subset table?

3. For the formulas constructed in Scenario 1 and 2, where within JMP should I apply/run the formula to be able to get the subset table?

ian_jmp

I'm sorry - We often use code a quick way to respond to questions, but to be clear, you certainly don't need to use it to work with JMP.

To see if I'm on the right track, though, please select the code above and copy it (to your clipboard). Then do 'New > New Script' and paste it into the resulting window. Then do 'Edit > Run Script'. After a short delay, this should make three tables, called 'In', 'Out' and 'In and Out Dates'. You can get the third one by using 'Tables > Join' on the first two, and joining by matching columns. If you need more help on how to do this, see: http://www.jmp.com/support/help/Join_Data_Tables.shtml. In the third table, double click on the column heading of "Criterion Met?". This is a JMP formula column (with a 'Value Order' property defined): http://www.jmp.com/support/help/Create_a_Formula_Column.shtml.

vinods20150

Hi Ian,

I hadn't mentioned earlier about my non-coding background, so fully understand you when you had sent me the response. Thank you.

I tried as you said.. Here are the outputs I get

An observation that I have on the tables

- Each patient in both in and out table has only one visit time-stamp and

- All patients figure in both tables (in and out)

As per the scenario details that I added on top of your first reply...trying to articulate below...

a. you will notice from the scenario table and data structure provided, that each patient has multiple visits in table 1 (outpatient) and the same patient may have multiple visits or may not have even one visit in table 2 (inpatient).

b. The need for each of the inpatient visit date of a patient to be less than any of the outpatient visit dates for that patient (note that each inpatient visit date of a patient has to be compared with each of the outpatient visit date of the same patient)

c. For the outpatient rows that meet criteria b, apply the criteria that inpatient visit date is <= 14 days of outpatient visit date of the same patient (may have erroneously stated the opposite earlier. Sorry)

d. Whenever criteria c is met the row in Outpatient table against that patient need to be flagged as 1 (yes)

e. A new column needs to be appended in the Outpatient table after the criterion met flag column to capture the inpatient visit date (against which the criterion got met)

f. If there is no outpatient row that meets any of the inpatient visit date criteria b and c, then flag 0 in the Outpatient table.

Hope I am sufficiently clear.

ian_jmp

OK. I made up some data that I think is similar to that which you have to hand. Save the attachments, open the tables and run the saved script in each to look.

To check my understanding, you want to loop over the rows in 'Outpatient' as follows:

1/. The first row is 'ID = 1' and 'Out Date = 20Jan2014 01:28:05'.

2/. Find any row(s) in 'Inpatient' in which 'ID = 1' and 'In Date' is not more than 14 days earlier than 20Jan2014 01:28:05. I have the sense that you do not expect multiple matches, so I'm not sure if you want to allow for that possibility. But if there is a match, you want to add a new column to 'Outpatient' that contains the 'In Date' value in the current row (in this case row 1).

3/. Move to the next row in 'Outpatient' and repeat until the end of the table.

Is this correct, please?

Outpatient.jmp

Inpatient.jmp

vinods20150

Thank you Ian. From what you have written, I think you have got it right...Give me 24 hours to revert back to you after trying the files you sent me.

FYI from a business context, I am trying to find cases of patients who came for an Outpatient visit within 14 days of their inpatient visit. In the future I just need to tweak the number of days (gap) to get the required result-set based on business demand.

One of your statement "I have the sense that you do not expect multiple matches, so I'm not sure if you want to allow for that possibility.",

Every outpatient or inpatient visit is a separate case and a separate row in the tables....It is possible that a patient can have two outpatient appointments on same day, could be with same or different specialties. If that is what you meant by multiple matches then it is possible when you loop each inpatient visit date of the patient..