Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Our lab has several procedures that we follow before billing a test. I need to report on the volume flowing through each step weekly.
The data model is simple (although the dates below may be datetimes):
load * inline [
CaseID,kit_received, test_reported, data_entry_start, data_entry_end, ptp_start, ptp_end, auth_start, auth_end, billed_date, claim_submit_date
1,'01-01-2018', '01-15-2018', '01-01-2018', '01-02-2018', '01-02-2018', '01-06-2018', '01-06-2018', '01-11-2018', '01-15-2018', '01-15-2018'
2,'01-02-2018', '01-15-2018', '01-02-2018', '01-02-2018', '01-02-2018', '01-09-2018', '01-10-2018', '01-11-2018', '01-15-2018', '01-15-2018'
3,'01-02-2018', '01-09-2018', '01-03-2018', '01-03-2018', '01-04-2018', '01-06-2018', '01-06-2018', '01-13-2018', '01-14-2018', '01-15-2018'
4,'01-03-2018', '01-09-2018', '01-03-2018', '01-04-2018', '01-05-2018', '01-08-2018', '01-10-2018', '01-12-2018', '01-12-2018', '01-12-2018'
]
What I would want to show is a table like this:
or this:
However, I don't know how to create an independent time dimension and to match / interval match different fields to it. Any advice would be appreciated.
Hi, Maybe using a crosstable:
BASE:
load * inline [
CaseID,kit_received, test_reported, data_entry_start, data_entry_end, ptp_start, ptp_end, auth_start, auth_end, billed_date, claim_submit_date
1,'01-01-2018', '01-15-2018', '01-01-2018', '01-02-2018', '01-02-2018', '01-06-2018', '01-06-2018', '01-11-2018', '01-15-2018', '01-15-2018'
2,'01-02-2018', '01-15-2018', '01-02-2018', '01-02-2018', '01-02-2018', '01-09-2018', '01-10-2018', '01-11-2018', '01-15-2018', '01-15-2018'
3,'01-02-2018', '01-09-2018', '01-03-2018', '01-03-2018', '01-04-2018', '01-06-2018', '01-06-2018', '01-13-2018', '01-14-2018', '01-15-2018'
4,'01-03-2018', '01-09-2018', '01-03-2018', '01-04-2018', '01-05-2018', '01-08-2018', '01-10-2018', '01-12-2018', '01-12-2018', '01-12-2018'
];
Crosstable (FIELDS, DATE) TEST: LOAD * Resident BASE;
DROP TABLE BASE;