Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
toddbuss
Creator
Creator

How do I count missing records based on other existing records?

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:

FacilityDatepatientdata3, 4, 5, etc
1011/1/17jones
1011/1/17smith
1012/1/17smith
1022/1/17thomas
1032/1/17franklin
1013/1/17jones

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:

101102103
1/1/17200
2/1/17111
3/1/17100

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.  🙂

3 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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.

toddbuss
Creator
Creator
Author

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;

felipedl
Partner - Specialist III
Partner - Specialist III

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;