Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

compare fields within column

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:

DatePlan TimeIs Time
28.05.201705:03:3005:04:29
28.05.201705:05:4009:30:26
28.05.201705:10:0005:12:35
28.05.201705:14:0005: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!

Tags (2)
5 Replies
Highlighted

Re: compare fields within column

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

...

Highlighted
Contributor
Contributor

Re: compare fields within column

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

Highlighted

Re: compare fields within column

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

Highlighted

Re: compare fields within column

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

Highlighted

Re: compare fields within column

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;