Count Missing Utilization

[drew61199]

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! 

[Jobson_joseph]

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]

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];