previous value with condition

[Anonymous]

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

[sunny_talwar]

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;

[Anonymous]

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

[sunny_talwar]

Can you share the qvw to check it

[Anonymous]

yes, follow the link to download...

Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.

[Anonymous]

Hello Sunny, did you see it?

Tks

[Anonymous]

Any help with this please?

[Anonymous · Accepted solution]

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;