QlikView. Create your first app? ...
In this article, I want to describe the basics of using QlikView from QlikTech for Business Intelligence. Caution, a lot of spoilers.
A bit of foreword
QlikTech is a business intelligence software company headquartered in Radnor. It was founded in 1993 in Sweden.
QlikView is a BI platform with associative search in RAM with built-in ETL tools .
Abbreviations, Definitions, Information
- QV - QlikView
- It is customary to call not “report”, but “application” in QV
- There can be as many reports in one application
- Under the reports are defined - tables, graphs, lists, charts
Actually, why are we here? - In order to create your first app for simple sales analysis
What do we need for this?
1. QlikView.
2. Data, I created it randomly using QV.
3. 25-30 minutes of time.
References
1. QlikView Personal Edition (ver. 11.20 SR5) x86 x64 Win8andUp - Free version, available to everyone, without restrictions for use by one user on one computer
2. Data archive on Mega
Data loading
We assume that you have already installed QV and created a new document.
We go into the script editor (CTRL + E) and see that we have already created the default environment variables:
Script Editor Window
All that we need today to work is a button -
because we will load data from Excel files. Let's create a calendar first, type in the code:
(the full script will be under the cut at the end of the article)
The calendar
Do not forget to save the application every time before downloading data, otherwise you will have to type all the code again.
You can enable auto save in the settings.
Now let's do the data loading by pressing CTRL + R
progress
Close it and we see the sheet properties window,
sheet properties
We are offered to change the composition of the displayed data on the sheet based on what we have, so let's take advantage.
Add Year, Month
it should be
and click OK, arrange our "listboxes" as we like:
moving objects
That's all, go to the CTRL + E script editor and click on the button - Table Files, select the Nomenclature file and click open:
directory
we won’t change any properties, but just click Finish (or Finish)
spreadsheet document properties
at the same time, we automatically created a code for loading data from a document:
item code
All we need to do is replace the first line of Directory; to the Nomenclature
and add the line RowNo () as ID, I specifically made it here, because very often when loading data we don’t have unique identifiers by which we can bind data, and you have to create them manually, and even sometimes using 5- 6 fields, but more on that another time.
as should be with the nomenclature
We continue, add to the script the download of sales data similar to Nomenclature
as it should be with sales
We are done with the script, save the CTRL + S application, and load CTRL + R, pay attention to how quickly you downloaded 50 thousand. rows from an excel file.
progress
Design
Let's leave the result unchanged
sheet data
and create a pivot table
full table
We chose the type of table - summary, click next, add measurements,
click next, add expressions:
We chose the type of table - summary, click next, add measurements,
click next, add expressions:
Let's straighten the table a bit and get something similar to this.
Something like this
what we got:
checked,
go to the properties:
for the first 4 measurements we set individually Show Partial Sums on the Presentation tab
checked,
go to the properties:
for the first 4 measurements we set individually Show Partial Sums on the Presentation tab
We continue the transformations, since nothing is clear now.
images
and just remove the measurements of the year, month and day, and leave only the Parent and Nomenclature
and just remove the measurements of the year, month and day, and leave only the Parent and Nomenclature
It began to look more or less clear, excellent.
Now you can play with the years and months, just select the one you need, and pay attention to the speed of data conversion in the table
sampling
Sample Clear Button: 
The end.
Conclusion
Actually, I wanted to show the community how it works and build applications in QV on the fingers.
I myself am now engaged in more serious projects on QV, in particular, building reports on 500 million lines of data.
If the continuation will be interested in the habroad community, then I will gladly provide it.
Welcome everyone to kamenty.
full application code
SET ThousandSep = '';
SET DecimalSep = ',';
SET MoneyThousandSep = '';
SET MoneyDecimalSep = ',';
SET MoneyFormat = '# ## 0.00 p.; - # ## 0.00 p.';
SET TimeFormat = 'h: mm: ss';
SET DateFormat = 'DD.MM.YYYY';
SET TimestampFormat = 'DD.MM.YYYY h: mm: ss [.fff]';
SET MonthNames = 'Jan; Feb Mar Apr May; Jun Jul Aug Sep Oct but I; Dec ';
SET DayNames = 'Mon; Tue Wed; Thu Fri Sat; Sun ';
// empty calendar table
Calendar: / * Name of our table * /
load * Inline / * We load the data line by line, we will indicate the data explicitly * /
[Date, Month, Year, Day];
Let CurrentDate = Num (Today (1)); / * Define the variable CurrentDate -
as today's number in digital representation * /
Let Period = 800; / * I took an arbitrary number, you can use any * /
For i = 0 to $ (Period) / * create a cycle * /
Concatenate (Calendar) / * Concatenate command means that
we need to add new data to the already created table * /
Load / * The Load command is used almost everywhere, it means loading data into the table * /
Date ($ (CurrentDate) - $ (i)) as Date, / * Calculate the Date * /
Month (Date ($ (CurrentDate) - $ (i)) ) as Month, / * Calculate Month * /
Year (Date ($ (CurrentDate) - $ (i))) as Year, / * Calculate Year * /
Day (Date ($ (CurrentDate) - $ (i))) as Day / * Calculate the Number * /
AutoGenerate 1 / * Autogenerate 1 - means that we need to write one row to the table * /
;
Next i; / * continue the cycle * /
Nomenclature:
LOAD Name of the Product,
Parent,
RowNo () as ID
FROM
nomenclature.xlsx
(ooxml, embedded labels, table is Sheet1);
Movements:
LOAD ID,
Date,
Quantity,
Amount
FROM
Movements.csv
(txt, utf8, embedded labels, delimiter is ',', msq);
SET DecimalSep = ',';
SET MoneyThousandSep = '';
SET MoneyDecimalSep = ',';
SET MoneyFormat = '# ## 0.00 p.; - # ## 0.00 p.';
SET TimeFormat = 'h: mm: ss';
SET DateFormat = 'DD.MM.YYYY';
SET TimestampFormat = 'DD.MM.YYYY h: mm: ss [.fff]';
SET MonthNames = 'Jan; Feb Mar Apr May; Jun Jul Aug Sep Oct but I; Dec ';
SET DayNames = 'Mon; Tue Wed; Thu Fri Sat; Sun ';
// empty calendar table
Calendar: / * Name of our table * /
load * Inline / * We load the data line by line, we will indicate the data explicitly * /
[Date, Month, Year, Day];
Let CurrentDate = Num (Today (1)); / * Define the variable CurrentDate -
as today's number in digital representation * /
Let Period = 800; / * I took an arbitrary number, you can use any * /
For i = 0 to $ (Period) / * create a cycle * /
Concatenate (Calendar) / * Concatenate command means that
we need to add new data to the already created table * /
Load / * The Load command is used almost everywhere, it means loading data into the table * /
Date ($ (CurrentDate) - $ (i)) as Date, / * Calculate the Date * /
Month (Date ($ (CurrentDate) - $ (i)) ) as Month, / * Calculate Month * /
Year (Date ($ (CurrentDate) - $ (i))) as Year, / * Calculate Year * /
Day (Date ($ (CurrentDate) - $ (i))) as Day / * Calculate the Number * /
AutoGenerate 1 / * Autogenerate 1 - means that we need to write one row to the table * /
;
Next i; / * continue the cycle * /
Nomenclature:
LOAD Name of the Product,
Parent,
RowNo () as ID
FROM
nomenclature.xlsx
(ooxml, embedded labels, table is Sheet1);
Movements:
LOAD ID,
Date,
Quantity,
Amount
FROM
Movements.csv
(txt, utf8, embedded labels, delimiter is ',', msq);