Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
samuelandlopes
Creator
Creator

Distinct count with 2 conditions

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

     

MACHINEWO_NOREAL_S_DATEREAL_F_DATECount(WO_NO)Correct failure count
1210
FW4712567802/10/2017 07:3002/10/2017 07:4811
FW4712575203/10/2017 07:3003/10/2017 09:0011
FW4712575703/10/2017 09:0003/10/2017 12:5011
FW4712571403/10/2017 14:3003/10/2017 15:0011
FW4712571103/10/2017 15:0003/10/2017 16:0010
FW4712642005/10/2017 13:4205/10/2017 14:0611
FW4712763906/10/2017 14:3006/10/2017 15:0011
FW4712763806/10/2017 15:0006/10/2017 16:0010
FW4712717208/10/2017 15:0608/10/2017 16:5411
FW4760202709/10/2017 07:2409/10/2017 11:0011
1 Solution

Accepted Solutions
its_anandrjs

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

];

View solution in original post

14 Replies
sunny_talwar

May be like this

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

samuelandlopes
Creator
Creator
Author

didn't work:

Anil_Babu_Samineni

May be typo error?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
samuelandlopes
Creator
Creator
Author

Got error

its_anandrjs

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

samuelandlopes
Creator
Creator
Author

also getting error with this expression...

ERROR.JPG

its_anandrjs

May be try with NUM dates though


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


Or


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

effinty2112
Master
Master

Hi Samuel,

Maybe:

MACHINE WO_NO REAL_S_DATE REAL_F_DATE if(REAL_S_DATE= Above(TOTAL REAL_F_DATE),0,1)
FW4712567802/10/2017 07:3002/10/2017 07:481
FW4712575203/10/2017 07:3003/10/2017 09:001
FW4712575703/10/2017 09:0003/10/2017 12:500
FW4712571403/10/2017 14:3003/10/2017 15:001
FW4712571103/10/2017 15:0003/10/2017 16:000
FW4712642005/10/2017 13:4205/10/2017 14:061
FW4712763906/10/2017 14:3006/10/2017 15:001
FW4712763806/10/2017 15:0006/10/2017 16:000
FW4712717208/10/2017 15:0608/10/2017 16:541
FW4760202709/10/2017 07:2409/10/2017 11:001

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

=7

Regards

Andrew

samuelandlopes
Creator
Creator
Author

same error:

ERROR 2.JPG