Distinct count with 2 conditions

[Anonymous]

Hello,

I need to count the number of failures with 2 conditions:

If the machine is the same, and also the start day/time is equal the final end/time of the previous order, should not count as a failure.

Follow the files and example.

I need to bring the correct failure count as you guys can see in the last column.

wo_no = work order number

Thanks a lot

MACHINE	WO_NO	REAL_S_DATE	REAL_F_DATE	Count(WO_NO)	Correct failure count
				12	10
FW47	125678	02/10/2017 07:30	02/10/2017 07:48	1	1
FW47	125752	03/10/2017 07:30	03/10/2017 09:00	1	1
FW47	125757	03/10/2017 09:00	03/10/2017 12:50	1	1
FW47	125714	03/10/2017 14:30	03/10/2017 15:00	1	1
FW47	125711	03/10/2017 15:00	03/10/2017 16:00	1	0
FW47	126420	05/10/2017 13:42	05/10/2017 14:06	1	1
FW47	127639	06/10/2017 14:30	06/10/2017 15:00	1	1
FW47	127638	06/10/2017 15:00	06/10/2017 16:00	1	0
FW47	127172	08/10/2017 15:06	08/10/2017 16:54	1	1
FW47	602027	09/10/2017 07:24	09/10/2017 11:00	1	1

[Anonymous]

hi Andrew,

getting this error:

Am I doing something wrong?

[its_anandrjs · Accepted solution]

On script part do calculation and UI do count of expression.

LOAD *,IF(REAL_S_DATE = Previous(REAL_F_DATE),1,0) as [Correct Failure];

LOAD MACHINE, WO_NO,

(Timestamp( Timestamp#( REAL_S_DATE,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as REAL_S_DATE,

(Timestamp( Timestamp#( REAL_F_DATE,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm')) as REAL_F_DATE, CWO_NO, [Correct failure count];

LOAD * Inline

[

MACHINE, WO_NO, REAL_S_DATE, REAL_F_DATE, CWO_NO, Correct failure count

FW47, 125678, 02/10/2017 07:30, 02/10/2017 07:48, 1, 1

FW47, 125752, 03/10/2017 07:30, 03/10/2017 09:00, 1, 1

FW47, 125757, 03/10/2017 09:00, 03/10/2017 12:50, 1, 1

FW47, 125714, 03/10/2017 14:30, 03/10/2017 15:00, 1, 1

FW47, 125711, 03/10/2017 15:00, 03/10/2017 16:00, 1, 0

FW47, 126420, 05/10/2017 13:42, 05/10/2017 14:06, 1, 1

FW47, 127639, 06/10/2017 14:30, 06/10/2017 15:00, 1, 1

FW47, 127638, 06/10/2017 15:00, 06/10/2017 16:00, 1, 0

FW47, 127172, 08/10/2017 15:06, 08/10/2017 16:54, 1, 1

FW47, 602027, 09/10/2017 07:24, 09/10/2017 11:00, 1, 1

];

[effinty2112]

Hi Samuel,

The expression

Sum(Aggr(if(REAL_S_DATE= Above(TOTAL REAL_F_DATE),0,1),(REAL_S_DATE,(NUMERIC,ASCENDING))))

belongs in a text box and with the data provided should return the value 7.

Kind regards

Andrew

[Anonymous]

Don't know what is going on, but I'm getting the same error.

[Anonymous]

hello Anand,

Used this one and got it!!

LOAD *,IF(REAL_S_DATE = Previous(REAL_F_DATE) and MACHINE = Previous(MACHINE) ,0,1) as n_failure;

Thank you so much!!!