topic Re: previous value with condition in QlikView

previous value with condition

Anonymous — Sat, 09 Dec 2017 23:18:04 GMT

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

Re: previous value with condition

sunny_talwar — Sat, 09 Dec 2017 23:35:51 GMT

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;

Re: previous value with condition

Anonymous — Sat, 09 Dec 2017 23:49:33 GMT

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

Re: previous value with condition

sunny_talwar — Sun, 10 Dec 2017 02:40:19 GMT

Can you share the qvw to check it

Re: previous value with condition

Anonymous — Sun, 10 Dec 2017 14:09:07 GMT

yes, follow the link to download...

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

Re: previous value with condition

Anonymous — Thu, 14 Dec 2017 17:22:03 GMT

Hello Sunny, did you see it?

Tks

Re: previous value with condition

Anonymous — Thu, 03 May 2018 22:52:04 GMT

Any help with this please?

Re: previous value with condition

Anonymous — Thu, 24 May 2018 13:56:42 GMT

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;