Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

toddbuss
New Contributor III

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
Valued Contributor III

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

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
New Contributor III

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

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
Valued Contributor III

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

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;