Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a graphic table as this
reqSIT/ID | reqOper/ID | encamReq/NOME_OPER | encamReq/DATA | result/DATA_FIM_CARGA | result/TIPO_RESULTADO | tempo Resp ( D HH:mm:ss) |
411:16:05 | ||||||
3008 | 43008 | MMMMM | 12/09/2016 10:52:24 | 07/10/2016 14:44:58 | P | 603:51:41 |
3008 | 43008 | MMMMM | 12/09/2016 10:52:24 | 11/10/2016 18:29:15 | P | 703:36:27 |
3009 | 43009 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:10 | P | 603:51:41 |
3010 | 43010 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:39 | P | 603:51:42 |
3011 | 43011 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:44:02 | N | 603:51:37 |
3012 | 43012 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:31 | P | 603:51:42 |
3013 | 43013 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:22 | P | 603:51:41 |
3014 | 43014 | MMMMM | 12/09/2016 10:52:26 | 07/10/2016 14:44:05 | N | 603:51:37 |
3015 | 43015 | MMMMM | 12/09/2016 10:52:26 | 07/10/2016 14:44:03 | N | 603:51:37 |
The first two rows show same values, only difference is in result/DATA_FIM_CARGA. I must show only the higher result/DATA_FIM_CARGA. How can i do that?
Should be:
reqSIT/ID | reqOper/ID | encamReq/NOME_OPER | encamReq/DATA | result/DATA_FIM_CARGA | result/TIPO_RESULTADO | tempo Resp ( D HH:mm:ss) |
411:16:05 | ||||||
3008 | 43008 | MMMMM | 12/09/2016 10:52:24 | 11/10/2016 18:29:15 | P | 703:36:27 |
3009 | 43009 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:10 | P | 603:51:41 |
3010 | 43010 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:39 | P | 603:51:42 |
3011 | 43011 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:44:02 | N | 603:51:37 |
3012 | 43012 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:31 | P | 603:51:42 |
3013 | 43013 | MMMMM | 12/09/2016 10:52:25 | 07/10/2016 14:45:22 | P | 603:51:41 |
3014 | 43014 | MMMMM | 12/09/2016 10:52:26 | 07/10/2016 14:44:05 | N | 603:51:37 |
3015 | 43015 | MMMMM | 12/09/2016 10:52:26 | 07/10/2016 14:44:03 | N | 603:51:37 |
One solution through Script..
tab1:
LOAD "reqSIT/ID", "reqOper/ID", "encamReq/NOME_OPER", "encamReq/DATA",
Timestamp#("result/DATA_FIM_CARGA",'MM/DD/YYYY hh:mm:ss') As "result/DATA_FIM_CARGA",
"result/TIPO_RESULTADO", "tempo Resp ( D HH:mm:ss)"
;
LOAD * INLINE [
"reqSIT/ID", "reqOper/ID", "encamReq/NOME_OPER", "encamReq/DATA", "result/DATA_FIM_CARGA", "result/TIPO_RESULTADO", "tempo Resp ( D HH:mm:ss)"
, , , , , , 411:16:05
3008, 43008, MMMMM, 12/09/2016 10:52:24, 07/10/2016 14:44:58, P, 603:51:41
3008, 43008, MMMMM, 12/09/2016 10:52:24, 11/10/2016 18:29:15, P, 703:36:27
3009, 43009, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:10, P, 603:51:41
3010, 43010, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:39, P, 603:51:42
3011, 43011, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:44:02, N, 603:51:37
3012, 43012, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:31, P, 603:51:42
3013, 43013, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:22, P, 603:51:41
3014, 43014, MMMMM, 12/09/2016 10:52:26, 07/10/2016 14:44:05, N, 603:51:37
3015, 43015, MMMMM, 12/09/2016 10:52:26, 07/10/2016 14:44:03, N, 603:51:37
];
Right Join(tab1)
tab2:
LOAD [reqSIT/ID], [reqOper/ID],
Timestamp(Max("result/DATA_FIM_CARGA"),'MM/DD/YYYY hh:mm:ss') As "result/DATA_FIM_CARGA"
Resident tab1
Group By [reqSIT/ID], [reqOper/ID]
;
If I use:
if((Max(aggr(max([result/ID]), [reqOper/ID])))-[reqOper/ID]=0,[result/ID],(Max(aggr(max([result/ID]), [reqOper/ID]))))
It shows me :
reqSIT/ID | reqOper/ID | encamReq/NOME_OPERADORA | encamReq/DATA | result/DATA_FIM_CARGA | result/DATA_RECEBIMENTO | result/TIPO_RESULTADO | result/ID | tempo Resp ( Dias HH:mm:ss) | //if(Max(aggr(Sum([result/ID]), [reqOper/ID]))=null(),null(),[reqOper/ID]) //Max(aggr(max([result/ID]), [reqOper/ID])) //if([result/ID]= Max(aggr(Sum([result/ID]), [reqOper/ID] )), [result/ID], '0' ) if((Max(aggr(max([result/ID]), [reqOper/ID])))-[reqOper/ID]=0,[result/ID],(Max(aggr(max([result/ID]), [reqOper/ID])))) |
337:20:58 | |||||||||
1562 | 41562 | MMM | 19/01/2016 13:49:10 | 22/01/2016 11:46:09 | 22/01/2016 11:45:09 | N | 47502971 | 69:55:59 | 47698783 |
1562 | 41562 | MMM | 19/01/2016 13:49:10 | 05/02/2016 18:15:18 | 05/02/2016 18:15:10 | N | 47698763 | 412:26:00 | - |
1562 | 41562 | MMM | 19/01/2016 13:49:10 | 10/02/2016 15:40:54 | 10/02/2016 15:30:06 | P | 47698783 | 529:40:56 | - |
But still shows and count the null cell, I dont want it, I want to remove it, is there a way?
One solution through Script..
tab1:
LOAD "reqSIT/ID", "reqOper/ID", "encamReq/NOME_OPER", "encamReq/DATA",
Timestamp#("result/DATA_FIM_CARGA",'MM/DD/YYYY hh:mm:ss') As "result/DATA_FIM_CARGA",
"result/TIPO_RESULTADO", "tempo Resp ( D HH:mm:ss)"
;
LOAD * INLINE [
"reqSIT/ID", "reqOper/ID", "encamReq/NOME_OPER", "encamReq/DATA", "result/DATA_FIM_CARGA", "result/TIPO_RESULTADO", "tempo Resp ( D HH:mm:ss)"
, , , , , , 411:16:05
3008, 43008, MMMMM, 12/09/2016 10:52:24, 07/10/2016 14:44:58, P, 603:51:41
3008, 43008, MMMMM, 12/09/2016 10:52:24, 11/10/2016 18:29:15, P, 703:36:27
3009, 43009, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:10, P, 603:51:41
3010, 43010, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:39, P, 603:51:42
3011, 43011, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:44:02, N, 603:51:37
3012, 43012, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:31, P, 603:51:42
3013, 43013, MMMMM, 12/09/2016 10:52:25, 07/10/2016 14:45:22, P, 603:51:41
3014, 43014, MMMMM, 12/09/2016 10:52:26, 07/10/2016 14:44:05, N, 603:51:37
3015, 43015, MMMMM, 12/09/2016 10:52:26, 07/10/2016 14:44:03, N, 603:51:37
];
Right Join(tab1)
tab2:
LOAD [reqSIT/ID], [reqOper/ID],
Timestamp(Max("result/DATA_FIM_CARGA"),'MM/DD/YYYY hh:mm:ss') As "result/DATA_FIM_CARGA"
Resident tab1
Group By [reqSIT/ID], [reqOper/ID]
;