topic compare fields within column in QlikView

compare fields within column

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: compare fields within column

rubenmarin — Thu, 04 Jan 2018 10:20:49 GMT

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

...

Re: compare fields within column

Anonymous — Thu, 04 Jan 2018 10:28:22 GMT

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

Re: compare fields within column

sunny_talwar — Thu, 04 Jan 2018 13:42:58 GMT

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

Re: compare fields within column

sunny_talwar — Thu, 04 Jan 2018 14:06:11 GMT

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

Re: compare fields within column

rubenmarin — Fri, 05 Jan 2018 12:51:46 GMT

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;