topic Distinct count with 2 conditions in QlikView

Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 14:19:42 GMT

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

Re: Distinct count with 2 conditions

sunny_talwar — Mon, 16 Oct 2017 14:53:21 GMT

May be like this

Count(If(REAL_S_DATE) <> Above(REAL_F_DATE), WO_NO)

Re: Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 15:00:23 GMT

didn't work:

Re: Distinct count with 2 conditions

Anil_Babu_Samineni — Mon, 16 Oct 2017 15:02:43 GMT

May be typo error?

Count(If(REAL_S_DATE <> Above(REAL_F_DATE), WO_NO))

Re: Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 15:08:44 GMT

Got error

Re: Distinct count with 2 conditions

its_anandrjs — Mon, 16 Oct 2017 15:15:50 GMT

Checking Machine also

Count(If(REAL_S_DATE) <> Above(REAL_F_DATE) and MACHINE <> Above(MACHINE), WO_NO)

This is correct

Count(If(REAL_S_DATE) <> Above(REAL_F_DATE) and MACHINE <> Above(MACHINE), WO_NO))

Re: Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 15:29:06 GMT

also getting error with this expression...

Re: Distinct count with 2 conditions

its_anandrjs — Mon, 16 Oct 2017 15:31:36 GMT

May be try with NUM dates though

Count( If NUM( REAL_S_DATE ) <> Above( NUM(REAL_F_DATE)) and MACHINE <> Above(MACHINE), WO_NO))

Count( If NUM( REAL_S_DATE ) <> Above( NUM(REAL_F_DATE)) , WO_NO))

Re: Distinct count with 2 conditions

effinty2112 — Mon, 16 Oct 2017 15:38:26 GMT

Hi Samuel,

Maybe:

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

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

Regards

Andrew

Re: Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 15:39:37 GMT

same error:

Re: Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 15:44:38 GMT

hi Andrew,

getting this error:

Am I doing something wrong?

Re: Distinct count with 2 conditions

its_anandrjs — Mon, 16 Oct 2017 15:58:21 GMT

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

];

Re: Distinct count with 2 conditions

effinty2112 — Mon, 16 Oct 2017 16:00:46 GMT

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

Re: Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 16:37:36 GMT

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

Re: Distinct count with 2 conditions

Anonymous — Mon, 16 Oct 2017 16:38:47 GMT

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!!!