Ordering, peeking, and matching all at once
by Barry Harmsen, Miguel Ángel García
QlikView for Developers
- QlikView for Developers
- Table of Contents
- QlikView for Developers
- Credits
- About the Authors
- Acknowledgements
- About the Reviewer
- www.PacktPub.com
- Customer Feedback
- Preface
- 1. Meet QlikView
- 2. What's New in QlikView 12?
- 3. Seeing is Believing
- 4. Data Sources
- 5. Data Modeling
- 6. Styling Up
- 7. Building Dashboards
- User types
- Applying the DAR principle to Airline Operations
- Creating the Analysis sheet
- Creating the new Dashboard sheet
- Creating the Reports sheet
- Other charts
- Summary
- 8. Scripting
- The Script Editor
- Script statements
- Conditional functions
- Dealing with different data types
- Debugging script
- Standardizing and organizing script
- Re-using scripts
- Managing file locations and connection strings
- Summary
- 9. Data Modeling Best Practices
- 10. Basic Data Transformation
- 11. Advanced Expressions
- Using variables
- Using the TOTAL qualifier
- The Aggr function
- Conditional functions
- A tip on copying expressions
- Summary
- 12. Set Analysis and Point In Time Reporting
- 13. Advanced Data Transformation
- 14. More on Visual Design and User Experience
- 15. Security
- Index
In the earlier sections, we have discussed three different functions commonly used in data transformation. We will now present a use case in which all three functions will complement each other to achieve a specific task.
We know that the IntervalMatch function makes use of closed intervals already defined in a table. What happens if all we have is a start date? To illustrate this scenario, look at the following screenshot:
As you can see, the End Date field has disappeared. However, there is a way for us to guess it and assign the corresponding value, based on the start date of the immediate following record. That is, if one record starts on 1-Feb-1998 and the immediate following starts on 1-Jan-2000, it means that the first interval ended on 31-Dec-1999, right?
In order for us to calculate the end date, we need to first sort the table values so that all corresponding records are contiguous, then "peek" at the start value from the next (or previous, if ordered backwards) record, subtract one day and that will be our new end date. After that, we are now able to use the IntervalMatch function to expand those intervals.
To complete the challenge, make use of the same Carrier Decode table we have used previously, only ignore the End Date field as if it was not there. You will also be able to compare your results with those we came up with in the previous section.
Good Luck!
-
No Comment