topic Re: Comparing successive data in QlikView

Comparing successive data

Thu, 29 May 2014 19:18:37 GMT

Hi!

I have a table with metered volumes pr day (date). I want to compare the values for subsequent days, and set a flag/comment if the values are the same. How do I do this in an expression in qlikview?

Ex:

Prod.date Volume Message

5/1/2014 105 OK

5/2/2014 107 OK

5/3/2014 107 Wrong

5/4/2014 114 OK

The logic is that I get a flag/message on 5/3/2014 as stated in example above.

BR, Frode

Re: Comparing successive data

julian_rodriguez — Thu, 29 May 2014 19:30:22 GMT

You can use the Peek function, on the load script.

Something like

Table_Example:

LOAD

[Prod. Date],

Volume,

If(Peek('Volume') = Volume, 'Wrong', 'OK') As Message

FROM (Your source);

It works if the table is correctly sorted

Regards,

Re: Comparing successive data

eduardo_sommer — Thu, 29 May 2014 19:31:30 GMT

Hi, Frode

Use this script (I changed the date format due to my region)

Table:

Load ProdDate,

Volume,

if(Volume=peek(Volume), 'Wrong', 'Ok') as Message;

Load * inline [

ProdDate, Volume

1/5/2014,105

2/5/2014,107

3/5/2014,107

4/5/2014,114

];

See the attached qvw

Eduardo

Re: Comparing successive data

Thu, 29 May 2014 19:59:36 GMT

Hi!

Thank you for your quick responses. However, peek did not give me the wanted result. I want to check the record before (or after, dependant of sorting) in an already loaded table.

BR, Frode

Re: Re: Comparing successive data

eduardo_sommer — Thu, 29 May 2014 20:08:06 GMT

I removed the peek and created a straight table with the Date as a dimension and Volume as the expression. Then, I added a expression

if (Volume=Above(Volume), 'Wrong', 'Ok')

and it worked.

See the new app attached

Eduardo

Re: Comparing successive data

eduardo_sommer — Thu, 29 May 2014 20:10:45 GMT

If you need to check the record below, just change the Above to Below.

Eduardo

Re: Comparing successive data

Thu, 29 May 2014 20:17:47 GMT

That did the trick, thank you!!

Re: Comparing successive data

eduardo_sommer — Thu, 29 May 2014 20:22:59 GMT

You're welcome, Frode