QlikView Load Data From Files – 10 Types of QlikView Data Files

1. Objective

QlikView offers a wide range of files from which data can import and analysed making it a wholesome tool. In this QlikView Load Data tutorial, we will discuss the different types of files from which data can load in QlikView along with learning how to load data in QlikView.

So, let’s start QlikView Load Data from Files.

QlikView Load Data From Files - 10 Types of QlikView Data Files

QlikView Load Data From Files – 10 Types of QlikView Data Files

2. QlikView Data Files

In QlikView, there are a total of 10 types of QlikView data files from which data can load. The QlikView Data Files are:

i. Delimited Files

Delimited files are also known as CSV files which stands for Comma Separated Values. In CSV files the field names and field values separated by a comma or a semicolon or tab. These files also refer to as delimited text or just text files as it contains plain text. The file extension is ‘.csv’. 

Do you know the Architecture of QlikView?

QlikView Types of Data Files

Types of Data Files

ii. Fixed Record Files

Fixed Record files have the data records or rows of equal length or byte size. In such files, the length of records is fixed and can specify in the script command by (fix n) expression, where n is the number of bytes or characters deciding the record length. The fixed files stores in the system with extension ‘.fix’.

iii. Dif Files

Dif files are called Data Interchange Format files. Such is said to be of predominant use in import/export tasks. They can only contain the data of a single spreadsheet. The extension ‘.dif’ use for Dif files.

iv. Excel (XLS) Files

Microsoft Excel files, also known as XLS files can load into QlikView. More precisely, XLS stands for Excel spreadsheet. As the name suggests XLS files store data create in MS Excel document. The extension use by XLS files is ‘.xls’.

Follow this link to know the best practice for Data Modeling in QlikView

v. Excel (XLSX) Files

XLSX files are the file extensions for XML spreadsheet format use to store data from MS Excel documents. For the XML part adds in the picture the extension use for these files are ‘.xlsx’.

vi. HTML Files

HTML or web files are the files containing information on a web page. As you must already know, HTML stands for HyperText Markup Language. Both ‘.htm’ or ‘.html’ is acceptable as extensions for web files.

vii. QVD Files

QVD files are the QlikView Data files that are unique to QlikView and can only read or write in QlikView. These files predominantly use as storage and as intermediate files by QlikView services. The extension used for such files is ‘.qvd’.

viii. QVX Files

QVX is a QlikView data exchange file. A QVX file is a file format that contains metadata about the data table along with the actual data to load in that table. QVX use in QlikView to ensure high performance while data loading as it will be faster to load data using QVX files by reading the instructions contain in the metadata about the structure of the table. The file extension is ‘.qvx’

ix. XML Files

XML is an extension, which uses for files called Extensible Markup Language. Such files contain information about a data file along with the file’s data. This file can share via the web. Much like HTML, XML format also uses tags to describe the data. The file extension is ‘.xml’.

x. KML Files

KML stands for Keyhole Markup Language. This file format uses for defining and containing geographical data which use by earth browsers. Similar to XML, KML use tag-based data describing the structure and used case-sensitive tags. There is a standard set of KML tags and only those must use to describe data in a KML file. The file extension is ‘.kml’.

3. QlikView Load Data from Files

Data from any of the above discussed ten files can load into QlikView from the Script Editor. You can invoke the script editor either by the icon given on the toolbar or by Ctrl+E keys. In the script editor, selection of the data files takes place in a File Wizard which can invoke by two ways; one way is from the Insert option given at the top bar of the script editor. You can go to Insert>Load Statement>Load from file.

QlikView Load Data - Loading Data From Files in QlikView

Step.1 QlikView Load Data from Files

The second way is by using the options given at the bottom pane of the script editor under the name of Data from files. You can open the File Wizard by selecting Table Files.. option.

Have a Look – Types of Operators in QlikView

Binary [C:\users\hp\Desktop\Dataflair\products.qvw];
SalesMonthold:
INPUTFIELD Product_Id,
     Product_Line,
     Product_category,
     Product_quantity,
     Product_cost,
     [Store Location],
     City,
     Month;

LOAD Product_Id,
     Product_Line,
     Product_category,
     Product_quantity,
     Product_cost,
     [Store Location],
     City,
     Month
FROM
[C:\Users\HP\Desktop\Dataflair\Salesold.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Concatenate
SalesMonthNew:
LOAD Product_Id,
     Product_Line,
     Product_category,
     Product_quantity,
     Product_cost,
     [Store Location],
     City,
     Month
FROM
QlikView Load Data - Loading Data From Files in QlikView

Step.2 QlikView Load Data from Files

QlikView Quiz

4. How to Load Data in QlikView?

Below are the few steps, which help us to load data in QlikView:

i. Step One

The first step in loading data from files is to browse and select a file from the Open Local Files dialog. Set the file type to All table files if you want to browse and select from all available file types. Select a file and click Open.

QlikView Load Data - Loading Data From Files in QlikView

Step.3 QlikView Load Data from Files

Let’s Explore QlikView Tables and how to create different types of QlikView Tables?

ii. Step Two

Next, you will see a File Wizard: Type dialog which has two panes; a file type pane showing the entire list of file types and a table preview pane along with some other options related to the table. The file type of the file selected by you will read by the tool and will indicate in this dialog with a radio bullet in front the name of that type. Also, the preview pane will display how QlikView has interpreted the data in the tabular structure. You can select Embedded Labels form the Labels option to display the field names in the first row of the table.

 QlikView Load Data

Step.5 QlikView Load Data from Files

iii. Step Three

The File Wizard has several other tabs as well but usually, those properties are not needed to change and so you can click on Finish just after seeing the table preview and settings some options in this dialog. Upon clicking Finish, you will exit the wizard and the table statement will load in the script. Save this script and the QlikView document as well (if not saved already). In order to use this data in QlikView, reload (Ctrl+R) the script to fetch and use the loaded data.

LOAD [Order ID],
     [Custmer ID],
     [Place ID],
     [Product ID],
     [Sales ID],
     Sales,
     Quantity,
     Discount,
     Profit   
FROM
[C:\Users\HP\Desktop\Dataflair\Sale details.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
QlikView Load Data

Step.6 QlikView Load Data from Files

iv. Step Four

If, instead of clicking on Finish in the File Wizard: Type dialog, you choose to continue with exploring more options then click on Next>. You will see a File Wizard: Transformation dialog, continuing with which you can transform the newly loaded data in certain ways. We have a dedicated tutorial article on this topic on Data Transformation Wizard which you can refer to if you want to know about this step in detail.

 QlikView Load Data

Step.7 QlikView Load Data from Files

v. Step Five

Upon clicking Next>, we come to the next dialog which is File Wizard: Options dialog. Here, you can see the results of the operations you did while transforming the data in the previous step. Along with this, you can edit the Label settings, add Where clauses, and set parameters for crosstables and hierarchy tables.

Do you know about QlikView Time Chart Wizard?

QlikView Load Data

Step.8 QlikView Load Data from Files

Vi. Step Six

On continuing in the file wizard, the next dialog is File Wizard: Script dialog. Here, you can set how the script should look in the script editor. You can set a Max Line Length for ease of interpretation and using the Load All(*) option, you can command it to load all the fields from the loaded file into the script.

 QlikView Load Data

Step.9 QlikView Load Data from Files

So, this was all about QlikView Load Data. Hope you like our explanation.

5. Conclusion

So, this concludes our tutorial on QlikView Load Data. In this lesson, we saw what are the different file types form which data can load into QlikView. We also learned how to load files from the system into QlikView. We hope you can smoothly load any type of file into QlikView now, with the methods we just learned. Still, have a confusion regarding QlikView Load Data? Feel free to ask in the comment box.

Related Topic – QlikView Dollar Sign Expansion in QlikView

Reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.