Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
...
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
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
Look at the Peek() vs Previous() – When to Use Each to do this kind of thing in the script
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;