QlikView Delimited File (CSV) – Loading XML & Web Files in QlikView

In our last tutorial, we studied QlikView Files (QVD).  Now, we talk more deeply about files: QlikView Delimited File, QlikView XML File, and QlikView Web Files. Moreover, we will learn how to load delimited and web files in QlikView.

So, let’s start QlikView Delimited File.

QlikView Delimited File (CSV File)

A QlikView delimited file is a simple text file in which the data fields are separated by a comma, semicolon, tabs etc. Such files are called CSV (comma separated values) files and they also save in .csv format extension. You can load CSV files in QlikView. Such data files are useful as they can store huge data in a simple text form.

Do you know what is QlikView Trellis and how to apply it?

a. How to Load QlikView Delimited file (CSV)?

Following are the steps to load delimited or CSV files in Qlikview.

• Press CTRL+E to get the script editor on the screen.

• Go to INSERT and select LOAD STATEMENT then select LOAD FROM FILE.

• Choose a CSV (.csv) file from a proper path.

• You can choose a CSV data file from the TABLE FILES option present at the bottom right of the script editor.

• Next, you will see a File Wizard dialogue box. Select EMBEDDED LABELS in the LABELS tab to set the first row as the field names. If you notice at the left of this box. You will that the file type is automatically selected as DELIMITED. Next click on FINISH.

Let’s Explore the features of QlikView November2017 (12.2)

• You will now have a script in front of you with the field names added to it. Click on OK.

SET MoneyFormat='$#,##0.00; ($#, ##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
LOAD Series_reference,
     Period,
     Data_value,
     STATUS,
     UNITS,
     Subjects,
     Gruops,
     Series_title_1
FROM
[C:\Users\admin\consumers-price-index-Jun18-index-numbers-csv-tables.csv.csv]
(txt, codepage is 1252, embedded labels, delimiter is',', msq);

• Now right-click anywhere on the QlikView main screen and select NEW SHEET OBJECT then select TABLE BOX.

• A NEW TABLE BOX will appear where you can do all the operations as discussed in the table box paragraph of Excel file.

• You will finally see a table box on the main tab.

Follow this link to know how to create QlikView Dashboard?  

QlikView XML File

XML stands for Extensive Markup Language. It is markup language for data. As HTML markup tags shows the structure or layout of the webpage, XML file’s markup tags contains data and the meaning of that data. You can share the data on the XML file.

The QlikView XML files can share through the web, intranet etc. It uses the standard ASCII text and shares the data and file format.

a. How to Load XML Files in QlikView?

You can load XML files in QlikView. The steps are same as adding excel or delimited file followed by creating a Table Box and performing further operations on it.

Once you get your file loaded in the script. A save file prom will appear asking you the name and destination of the file. Click on next step once you are done.

SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00; ($#, ##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
// Start of [food.xml] LOAD statements
food:
LOAD name,
     Price,
     Discription,
     calories
FROM
[C:\Users\admin\Download\food.xml] (XmlSimple, Table is [breakfast_menu/food]);
// End of [food.xml] LOAD statements

Then perform the same steps to open and create the Table Box.

Do you know the different types of search options in QlikView?

QlikView Web Files

The web files are basically HTML files. The QlikView data loading option loads the HTML files from the URL given and understands the structure of the HTML file. The process of loading a web file in Qlikview is a little different.

a. How to Load Web Files in QlikView?

• Open the script editor and select WEB FILES from the DATA tab given at the bottom. Enter a URL in INTERNET FILES columns and click NEXT.

• Now a TYPE WIZARD will appear . Click NEXT twice if you don’t wish to make any changes. 

Must Read – QlikView Synthetic Keys

• Finally, the data will be loaded in the script editor. 

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
// Start of [food.xml] LOAD statements
food:
LOAD Rank,
     "[1]Country (or dependent territory)",
     Population,
     [% of population],
     "Averagerelativeannualgrowth (%) [2]",
     "Averageabsoluteannualgrowth [3]",
     "Estimateddoublingtime (Years) [4]", 
     [Officialfigures (whereavailable)],
     [Date oflast figure],
     [Regional grouping],
     Source
FROM
[https://en.wikipedia.org/wiki/List_of_European_counteries_by_population]
(html, utf8, embedded labels, table is @2);

• Save your file and open this HTML data in any layout you wish.

So, this was all about QlikView Delimited File (CSV). Hope you like our explanation.

Conclusion

We have learned to load all different sorts (QlikView Delimited File, XML and Web Files) of data files into QlikView. This data is used for visualization and analysis purposes to derive useful insights from it. Furthermore, if you have any query, feel to write to us.

Related Topic – QlikView SNMP

Reference