To not lose track in longer scripts, you have the possibility to structure the script. QlikView offers you to classify via index cards. Additionally we use easy functions to enrich our datamodel.
Generate an index sheet
To simplify the finding of ladelogics in the script, structure the script by creating new tabs or moving existing tabs. Try to find short but concise names for register riders to make navigation easier. It is especially useful to generate an “exit” register rider, which holds the command “EXIT SCRIPT;”. This makes it possible for complex data structures to stop “earlier” with the loading of the data and to analyze isolated logs.
In addition to the company structure, which is almost always present in all projects, “time” is the most important main feature of any analysis.At this point, you will get to know the simplest date functions, but in later lessons we will learn how to build and integrate a “standard calendar”.
The most common date functions that can be applied to a field with date content are:
Everyone of these functions is powerful, e.g different economic years can be displayed, or even pointers to the last full month are possible. A well thought out time axis is going to help you create reports, charts and tables later on. You use these functions as follows:
//Loading of the calculation table Bills: LOAD CustomerId, ConractId, BillNumber, BillDate, YEAR(BillDate) AS Year, MONTH(BillDate) AS Month, DAY(BillDate)AS Day, Amount, Price; SQL SELECT * FROM Bills;
Functions and formulas for “calculations”
Apart from countless other options, QLikView also offers the possibility to calculate while loading data. This works as follows:
//Loading of the calculation table Bills: LOAD CustomerId, ..., Amount, Price, Amount * Price * (1-Discount) AS BillsLineSum; SQL SELECT * FROM Bills;
You obviously always have the possibility to calculate such calculations on the interface, but performance wise it is faster to calculate them while loading data – if possible.
Controlstructures during loading time
From time to time you have In some cases, you may need to apply certain rules depending on the field content. For this, it is possible to work with “IF-THEN-ELSE” functions.
// Loading of the calculation table Bills: LOAD CutomerId, ..., Amount, Price, Amount * Price * (1-Discount) AS BillsLineSum, If(wildmatch(Produkt,'*Tennisb*')>0,'Tennis','Other') AS Division; SQL SELECT * FROM Bills;
Here you can see how we compare the content of the field <Product> with the pattern “*Tennisb*” (* can be any amount of characters) and if the amount of hits is greater than 0, the division “Tennis” gets written, if not then “other”.
If you intend to refine search patterns you can set the number of charcters to be compared via “?” where each “?” represents a character.
- Extend your script from lesson 3 to a moving table
- Build a “small” calendar with year, month and day
- Extend your Pivot by a breakdown by time