19 September, 2018
1.Loading from Excel, Txt files 2. Resident Load 3. Inline Load 4. Auto-generate Load 5. Mapping Load 6. Partial Reload 7. Buffer Load 8. Binary Load 9. Info Load 10. Bundle Info Load 11. Incremental Load 12. Cross Table Load 13. Generic Load 14. Interval Match Load 15. Preceding Load 16. Semantic Load
In some cases, we will need to read the same table more than once in a single script execution. This means, querying the database (or QVDs) and pulling data from it, and then reprocessing that same data after the first read in order to make it adequate for our data model. Since the data is being stored in RAM after each query during the script execution, we can use that RAM-stored data instead of going directly to the original data source. This is accomplished via a Resident load. Resident Load is used for loading the data from previously loaded tables.
Transact: LOAD Year, Month, Day, Sales, CustID FROM [E:\Qlik View\sales data.xls] (biff, embedded labels, table is Sheet1$);
Tab2: load Year, Sales Resident Transact ;
Inline load is used if data should be typed within the script, and not loaded from a file. Data entered through an inline clause must be enclosed by double Quotation Marks in Scripting or with square brackets. The text between these is interpreted in the same way as the content of a file.
If a piece of external information, such as a text file, a picture or a video is to be linked to a field value, this is done in a table that is loaded using an info prefix.
The table must contain two columns only, the first one with the field values that will form the keys to the information, the second one containing the information elements, e.g., the file names of the pictures etcetera.
Ex: Info LOAD * FROM flagoecd.csv;
The mapping prefix is used to create a mapping table that can be used for e.g., replacing field values and field names during the script run.
It can be put in front of a Load or Select (SQL) statement and will store the result of the loading statement as a mapping table.
A mapping table consists of two columns, the first containing comparison values and the second containing the desired mapping values.
Mapping tables are stored temporarily in the memory and dropped automatically after the script execution.
The content of the mapping table can be accessed using, e.g., the map … using statement, the rename field statement, the Applymap() function or the Map substring() function.
Examples: Mapping LOAD * inline [ No, Name 1, One 2, Two 3, Three ] ;
The most basic interval match is when you have a list of numbers or dates in one table – below called events – and a list of intervals in a second table. The goal is to link the two tables.
In the general case, this is a many-to-many relationship, i.e., an interval can have many dates belonging to it and a date can belong to many intervals. To solve this, you need to create a bridge table between the two original tables. The by far simplest way to solve this problem in QlikView is to use the Interval Match. It is a prefix that can be put in front of either a Load or a SELECT statement. The Load/SELECT statement needs to contain two fields only: the “From” and the “To” fields defining the intervals. The Interval Match prefix will then generate all combinations between the loaded intervals and a previously loaded numeric field, specified as a parameter to the prefix.
Typically, you would first load the table with the individual numbers (The Events), then the table with the Intervals, and finally an Interval Match that creates a third table that bridges the two first tables.
The resulting data model contains three tables:
The Events table that contains exactly one record per event. The Intervals table that contains exactly one record per interval. The bridge table that contains exactly one record per combination of event and interval, and that links the two previous tables.