Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I need to add in the script a column with the last hourmeter. See the table bellow in excel. The condition should be the same machine and fuel ID:
DATE | MACHINE | ACTUAL HOUR | PREVIOUS HOUR | ID | ID_DESC | LITERS |
18/11/2017 | HV75 | 18915,2 | 18885,5 | LB0002 | OLEO DE CORRENTE | 16 |
19/11/2017 | FW55 | 17963,5 | 18915,2 | LB0001 | DIESEL | 94 |
19/11/2017 | FW55 | 17969 | 17963,5 | LB0001 | DIESEL | 103 |
19/11/2017 | HV75 | 18929,3 | 17969 | LB0001 | DIESEL | 280 |
19/11/2017 | HV75 | 18929,3 | 18915,2 | LB0002 | OLEO DE CORRENTE | 11 |
20/11/2017 | FW55 | 17976,9 | 18929,3 | LB0001 | DIESEL | 149 |
20/11/2017 | FW55 | 17984,5 | 17976,9 | LB0001 | DIESEL | 53 |
20/11/2017 | HV75 | 18941,9 | 17984,5 | LB0001 | DIESEL | 204 |
20/11/2017 | HV75 | 18941,9 | 18929,3 | LB0002 | OLEO DE CORRENTE | 12 |
20/11/2017 | HV75 | 18953,1 | 18941,9 | LB0001 | DIESEL | 188 |
Thanks in advance!!!
Hi,
I got it using previous instead of peek and order by machine, id and actual hour.
Tks for everyone's attention and help.
Table:
LOAD * INLINE [
DATE, MACHINE, HOUR, ID, ID_DESC, LITERS
18/11/2017, HV75, 18915.2, LB0002, OLEO DE CORRENTE, 16
18/11/2017, FW55, 17963.5, LB0001, DIESEL, 94
19/11/2017, FW55, 17969, LB0001, DIESEL, 103
19/11/2017, HV75, 18929.3, LB0001, DIESEL, 280
19/11/2017, HV75, 18929.3, LB0002, OLEO DE CORRENTE, 11
20/11/2017, FW55, 179769, LB0001, DIESEL, 149
20/11/2017, FW55, 17984.5, LB0001, DIESEL, 53
20/11/2017, HV75, 18941.9, LB0001, DIESEL, 204
20/11/2017, HV75, 18941.9, LB0002, OLEO DE CORRENTE, 12
20/11/2017, HV75, 18953.1, LB0001, DIESEL, 188
];
FinalTable:
LOAD *,
If(MACHINE = Previous(MACHINE) and ID = Previous(ID), Previous(HOUR)) as [PREVIOUS HOUR]
Resident Table
Order By MACHINE, ID, HOUR;
DROP Table Table;
May be like this
Table:
LOAD * INLINE [
DATE, MACHINE, ACTUAL HOUR, ID, ID_DESC, LITERS
18/11/2017, HV75, 18915.2, LB0002, OLEO DE CORRENTE, 16
18/11/2017, FW55, 17963.5, LB0001, DIESEL, 94
19/11/2017, FW55, 17969, LB0001, DIESEL, 103
19/11/2017, HV75, 18929.3, LB0001, DIESEL, 280
19/11/2017, HV75, 18929.3, LB0002, OLEO DE CORRENTE, 11
20/11/2017, FW55, 179769, LB0001, DIESEL, 149
20/11/2017, FW55, 17984.5, LB0001, DIESEL, 53
20/11/2017, HV75, 18941.9, LB0001, DIESEL, 204
20/11/2017, HV75, 18941.9, LB0002, OLEO DE CORRENTE, 12
20/11/2017, HV75, 18953.1, LB0001, DIESEL, 188
];
FinalTable:
LOAD *,
If(MACHINE = Previous(MACHINE) and ID = Previous(ID), Peek('ACTUAL HOUR')) as [PREVIOUS HOUR]
Resident Table
Order By MACHINE, ID, DATE;
DROP Table Table;
Tks Sunny for the answer, but got some lines with no value: what could it be?
You see that it hasn't come the previous on 18/11, 13/11 and 12/11...
MACHINE | DATE | ID | ID_DESC | PREVIOUS HOUR | ACTUAL HOUR |
FW47 | 20/11/2017 | LB0001 | DIESEL | 19247,9 | 19262,4 |
FW47 | 19/11/2017 | LB0001 | DIESEL | 19237,3 | 19247,9 |
FW47 | 18/11/2017 | LB0001 | DIESEL | 19237,3 | |
FW47 | 17/11/2017 | LB0001 | DIESEL | 19207,3 | 19217 |
FW47 | 16/11/2017 | LB0001 | DIESEL | 19196,7 | 19207,3 |
FW47 | 16/11/2017 | LB0004 | OLEO DE MOTOR 10W40 | 19139,1 | 19208,1 |
FW47 | 16/11/2017 | LB0005 | OLEO MARBRAX HV-68 | 19113 | 19208,1 |
FW47 | 16/11/2017 | LB0006 | OLEO 85W140 G5 | 19208,1 | |
FW47 | 15/11/2017 | LB0001 | DIESEL | 19196,7 | 19196,7 |
FW47 | 14/11/2017 | LB0001 | DIESEL | 19184,4 | 19196,7 |
FW47 | 13/11/2017 | LB0001 | DIESEL | 19188,7 | |
FW47 | 12/11/2017 | LB0001 | DIESEL | 19176,1 | |
FW47 | 11/11/2017 | LB0001 | DIESEL | 19150 | 19163 |
FW47 | 10/11/2017 | LB0001 | DIESEL | 19139,1 | 19150 |
Can you share the qvw to check it
yes, follow the link to download...
Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.
Hello Sunny, did you see it?
Tks
Any help with this please?
Hi,
I got it using previous instead of peek and order by machine, id and actual hour.
Tks for everyone's attention and help.
Table:
LOAD * INLINE [
DATE, MACHINE, HOUR, ID, ID_DESC, LITERS
18/11/2017, HV75, 18915.2, LB0002, OLEO DE CORRENTE, 16
18/11/2017, FW55, 17963.5, LB0001, DIESEL, 94
19/11/2017, FW55, 17969, LB0001, DIESEL, 103
19/11/2017, HV75, 18929.3, LB0001, DIESEL, 280
19/11/2017, HV75, 18929.3, LB0002, OLEO DE CORRENTE, 11
20/11/2017, FW55, 179769, LB0001, DIESEL, 149
20/11/2017, FW55, 17984.5, LB0001, DIESEL, 53
20/11/2017, HV75, 18941.9, LB0001, DIESEL, 204
20/11/2017, HV75, 18941.9, LB0002, OLEO DE CORRENTE, 12
20/11/2017, HV75, 18953.1, LB0001, DIESEL, 188
];
FinalTable:
LOAD *,
If(MACHINE = Previous(MACHINE) and ID = Previous(ID), Previous(HOUR)) as [PREVIOUS HOUR]
Resident Table
Order By MACHINE, ID, HOUR;
DROP Table Table;