compare fields within column

[Anonymous]

Hello,

I'm new in QlikView and I've got a Problem:

My data is about scheduled and is Time and there are some wrong data sets because of measruments. An example:

Date	Plan Time	Is Time
28.05.2017	05:03:30	05:04:29
28.05.2017	05:05:40	09:30:26
28.05.2017	05:10:00	05:12:35
28.05.2017	05:14:00	05:15:54

I have to tag these wrong data sets and store them into a table for further treatment.

I load the data from a qvd which looks like above.

Now my question:

How can I compare two fields within a column?

Thanks for your help!

[rubenmarin]

Hi Sonja, what kind of comparison are you searching?

Dates are numbers internally (1=1 day; 0.5=12 hours) so you can do math operations with them:

LOAD [Is Time],

[Plan time],

[Is Time]-[Plan time] as Difference,

If([Is Time]>[Plan time], 'Late') as Status

...

[Anonymous]

I want to compare [Is Time] from the second row with  [Is Time] from the third row.

If [Is Time] from the second row is greater than [Is Time] from the third row, than I have a wrong data set, as in my example

[sunny_talwar]

What is wrong within the above rows? What exactly are you comparing? Not sure if you are comparing Plan Time vs Is Time or if the comparison is within one of those rows to previous row? If it is comparing a particular row to it's previous row, you can use Peek() or Previous to do this

Peek() vs Previous() – When to Use Each

[sunny_talwar]

Look at the Peek() vs Previous() – When to Use Each to do this kind of thing in the script

[rubenmarin]

Sorry for delay, as Sunny said for inter-record comparisons you can use peek or previous doing a sorted load by Date and [Plan Time] descendent (to check if the one before has a higher time), ie:

LOAD Date,

     [Plan Time],

     [Is Time],

     If(Peek(Date)=Date and [Is Time]<Peek([Is Time]), 'wrong') as status

Resident TableName

Order by Date, [Plan Time] desc;