Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Perhaps someone could point me in the right direction.
I'm loading a table that consists of report-dates and facility-names. It's something like this:
Facility | Date | patient | data3, 4, 5, etc |
---|---|---|---|
101 | 1/1/17 | jones | |
101 | 1/1/17 | smith | |
101 | 2/1/17 | smith | |
102 | 2/1/17 | thomas | |
103 | 2/1/17 | franklin | |
101 | 3/1/17 | jones |
What is supposed to happen is that each month, each facility appends the table with several records.
I need to create a matrix of ALL facilities and ALL dates that shows the number of reports received:
101 | 102 | 103 | |
---|---|---|---|
1/1/17 | 2 | 0 | 0 |
2/1/17 | 1 | 1 | 1 |
3/1/17 | 1 | 0 | 0 |
This will be used to show which facilities are failing to report. For example, for the 3 months depicted, Facility 101 submitted 100% and Facilities 102 and 103 submitted 33% of reports.
I think I need to make an aggregate key of facility and date and count the number of records for each, but I don't see how I can make an aggregate key for the records that are missing (the zero values). I don't want to use set analysis because the dates and facilities are dynamic.
Thanks for reading. 🙂
Hi Todd,
You can do this by simply counting the fields, grouping by Date and facility.
Attached an example, see if it helps you.
Felipe.
I managed to get the matrix (that I think I need).
I suspect I'm making this more complicated than I need to, but here's the load script I'm using...
ReportListTemp:
//get list of all dates
LOAD
distinct "Beginning Date" as DateList,
"Beginning Date"
Resident STAMI_DATA
where exists ("Beginning Date")
;
// get list of all Facilities
join LOAD
distinct "Facility" as FacilityList,
Facility
Resident STAMI_DATA
Where Exists("Facility")
;
ReportList:
//Make an aggregate Key of all Date and Facility combinations
Load
DateList&FacilityList as KeyDATEFAC,
"Facility",
"Beginning Date"
Resident ReportListTemp;
join load;
drop table ReportListTemp;
My script is as follows, but if yours works also, than its ok .
x:
Load * Inline
[
Facility,Date,patient
101,1/1/17,jones,
101,1/1/17,smith,
101,2/1/17,smith,
102,2/1/17,thomas,
103,2/1/17,franklin,
101,3/1/17,jones
];
date:
Load
Facility,
Date as DateT,
count(Facility) as CountFac // Counts the reports
Resident x
Group by Facility,Date;