Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
drew61199
Contributor
Contributor

Count Missing Utilization

I have  a data source with item utilization. There is an machine #, date, begin time, and end time. I have two different scenarios and ideally I need a total of "missing utilization" ....  A) begin time and end time are the same and B) no data exists at all for a date (meaning a machine was never logged into at all).

 

I can currently count the instances for scenario A, but I cannot count the instances for scenario B. I assume I'll also need to query a calendar table and then count the instances where no data exists for each item # (because conceivably the dates will exist in a 1:1 relationship), but I'm not really sure how to go about that, if it will even work, or if there is a better solution available. 

I appreciate any insight! 

Labels (1)
2 Replies
Jobson_joseph
Contributor II
Contributor II

Hi,
in load statement you can use isnull() on the field mapping to the date field. if there is null then return a flag, use the count of that flag in the UI.

RsQK
Creator
Creator

Hi, you can create a flag and use it in set analysis like this:

temp:
LOAD
machine_number,
date,
begin_time,
end_time,
FABS(MATCH(begin_time,end_time)) AS missing_utilization_flag
INLINE [
machine_number,date,begin_time,end_time
A123,01.07.2022,00:24:00,00:30:00
A123,02.07.2022,01:24:00,01:30:00
A123,03.07.2022,02:24:00,02:30:00
A123,04.07.2022,00:24:00,00:24:00
A123,05.07.2022,03:24:00,03:30:00
A123,06.07.2022,04:24:00,04:30:00
A123,07.07.2022,05:24:00,05:30:00
A123,08.07.2022,05:24:00,04:30:00
A123,09.07.2022,07:24:00,07:30:00
A123,10.07.2022,08:24:00,08:30:00
B123,03.07.2022,02:24:00,02:30:00
B123,04.07.2022,00:24:00,00:24:00
B123,05.07.2022,03:24:00,03:30:00
C123,05.07.2022,03:24:00,03:30:00
C123,06.07.2022,01:24:00,17:30:00
C123,07.07.2022,05:24:00,05:30:00
C123,08.07.2022,06:24:00,06:30:00
C123,09.07.2022,07:24:00,07:30:00
];

temp_key:
LOAD DISTINCT
machine_number,
date
RESIDENT temp;

LEFT JOIN (temp)
LOAD *,
machine_number & '|' & NUM(date) as machine_date.#key
RESIDENT temp_key;

DROP TABLE temp_key;

temp_missing_records:
LOAD
NUM(FIELDVALUE('date',RECNO())) AS date
AUTOGENERATE FIELDVALUECOUNT('date');

JOIN (temp_missing_records)
LOAD
FIELDVALUE('machine_number',RECNO()) AS machine_number
AUTOGENERATE FIELDVALUECOUNT('machine_number');

INNER JOIN (temp_missing_records)
LOAD
1 AS missing_utilization_flag
AUTOGENERATE 1;

temp_missing_records2:
LOAD
machine_number & '|' & NUM(date) as machine_date_conc.#key,
date,
machine_number,
missing_utilization_flag
RESIDENT temp_missing_records;

DROP TABLE temp_missing_records;

CONCATENATE (temp)
LOAD *,
machine_date_conc.#key as [machine_date.#key]
RESIDENT temp_missing_records2
WHERE NOT EXISTS([machine_date.#key],[machine_date_conc.#key]);

DROP TABLE temp_missing_records2;

DROP FIELD [machine_date_conc.#key];