Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table with rows for each procedure performed on a patient during inpatient hospital visit (Account Number). I need to add rows for days that the patient did not have a procedure so that I end up with at least one row for each day, starting with the patient's Admit Date and finishing with the patients last Procedure Date. Here's an example of one patient's procedures. The yellow rows are what I need to add.
I've created a calendar that fills in the missing dates but I can't figure out how to get the missing dates on one row with the Account Number.
You can generate the rows in your procedure table in the load script by using WHILE in the LOAD statement to iterate and generate all dates between the Admit Date and Procedure Date with a script similar to this:
DATA0:
LOAD
*,
[Procedure Date] AS PD, // Helper field so the original real Procedure Date can always be referenced
[Procedure Name] AS PN // Helper field so the original real Procedure Name can always be referenced
INLINE [
Admit Date,Account,Procedure Date,Procedure Name
2018-01-26,A,2018-01-28,ABC #1
2018-01-26,A,2018-01-29,ABC #2
2018-01-26,A,2018-01-31,ABC #3
2018-01-26,A,2018-02-06,ABC #4
2018-01-01,B,2018-01-05,DEF #1
];
PROCEDURE:
LOAD
[Admit Date],
Account,
Date(If( Peek('Account')<>Account, [Admit Date]-1, Peek('Procedure Date'))+1,'YYYY-MM-DD') AS [Procedure Date],
If( If( Peek('Account')<>Account, [Admit Date], Peek('Procedure Date'))+1 <> PD , '' , PN) AS [Procedure Name],
PD,
PN
RESIDENT
DATA0
WHILE
If( Peek('Account')<>Account, [Admit Date], Peek('Procedure Date')+1) <= PD
;
DROP TABLE DATA0;
DROP FIELDS PD,PN; // Drop helper fields