topic Re: Count Missing Utilization in App Development

Count Missing Utilization

drew61199 — Thu, 14 Jul 2022 04:46:57 GMT

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!

Re: Count Missing Utilization

Jobson_joseph — Thu, 14 Jul 2022 08:21:32 GMT

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.

Re: Count Missing Utilization

RsQK — Thu, 14 Jul 2022 08:30:05 GMT

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