create rows for missing dates

Margaret — Tue, 30 Jan 2018 20:16:36 GMT

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.

Re: create rows for missing dates

petter — Tue, 30 Jan 2018 22:09:11 GMT

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

topic create rows for missing dates in QlikView

create rows for missing dates

Re: create rows for missing dates