Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

samuelandlopes
Contributor

previous value with condition

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:

       

DATEMACHINEACTUAL HOUR

PREVIOUS HOUR

IDID_DESCLITERS
18/11/2017HV7518915,218885,5LB0002OLEO DE CORRENTE16
19/11/2017FW5517963,518915,2LB0001DIESEL94
19/11/2017FW551796917963,5LB0001DIESEL103
19/11/2017HV7518929,317969LB0001DIESEL280
19/11/2017HV7518929,318915,2LB0002OLEO DE CORRENTE11
20/11/2017FW5517976,918929,3LB0001DIESEL149
20/11/2017FW5517984,517976,9LB0001DIESEL53
20/11/2017HV7518941,917984,5LB0001DIESEL204
20/11/2017HV7518941,918929,3LB0002OLEO DE CORRENTE12
20/11/2017HV7518953,118941,9LB0001DIESEL188

Thanks in advance!!!

1 Solution

Accepted Solutions
samuelandlopes
Contributor

Re: previous value with condition

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;

9 Replies

Re: previous value with condition

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;

samuelandlopes
Contributor

Re: previous value with condition

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

      

MACHINEDATEIDID_DESCPREVIOUS HOURACTUAL HOUR
FW4720/11/2017LB0001DIESEL19247,919262,4
FW4719/11/2017LB0001DIESEL19237,319247,9
FW4718/11/2017LB0001DIESEL 19237,3
FW4717/11/2017LB0001DIESEL19207,319217
FW4716/11/2017LB0001DIESEL19196,719207,3
FW4716/11/2017LB0004OLEO DE MOTOR 10W4019139,119208,1
FW4716/11/2017LB0005OLEO MARBRAX HV-681911319208,1
FW4716/11/2017LB0006OLEO 85W140 G5 19208,1
FW4715/11/2017LB0001DIESEL19196,719196,7
FW4714/11/2017LB0001DIESEL19184,419196,7
FW4713/11/2017LB0001DIESEL 19188,7
FW4712/11/2017LB0001DIESEL 19176,1
FW4711/11/2017LB0001DIESEL1915019163
FW4710/11/2017LB0001DIESEL19139,119150

Re: previous value with condition

Can you share the qvw to check it

samuelandlopes
Contributor

Re: previous value with condition

samuelandlopes
Contributor

Re: previous value with condition

Hello Sunny, did you see it?

Tks

samuelandlopes
Contributor

Re: previous value with condition

Any help with this please?

qvraj123
Contributor II

Re: previous value with condition

20/11/2017, FW55, 17984.5, LB0001, DIESEL, 53

    20/11/2017, HV75, 18941.9, LB0001, DIESEL, 204


Hi Sunny, is it because we have duplicates in the ID - just trying to understand

samuelandlopes
Contributor

Re: previous value with condition

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;

Highlighted
qvraj123
Contributor II

Re: previous value with condition

HI Sunnay, I have a very complex application built by someone and I need to adjust or make it more better for performacne

do you think you might be able to help me? I really need help on this - can you please email me rpreddiz@yahoo.com

please let  me know