Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here is an easy approach to extracting the data model from a QlikView (QVW) file. The example documented uses QlikView Desktop, if you are using Qlik Sense then please click here.
Why would you use or need this kind of technique?
In a production environment you may find use of the output table script elements without the variables as they can be useful in load processes when transformed QVDs can be re-used by other applications. However, there are many times where you need to create something quickly for an ad-hoc piece of work or as a demonstration to show a customer with amended data to make it more relevant (changing product, department names etc). This approach can also be useful when working offline on the front end or application tuning where you do not want to deal with all of the complications of the ETL process.
Step 1 - Enter the file name and path for the QVW from which you wish to extract data
Step 2 - Enter the destination folder path where the extract data will be saved
Step 3 - Select the output format type
Step 4 - Save and reload the application
Please note these instructions are also included in the dashboard itself in case you forget where it came from. This is what the dashboard looks like.
It's as easy as that.
This page will collect all relevant release information about Power Tools for QlikView, and will be continuously updated with each new release and with any patches that are issued. This way we hope for a more consolidated view of the Power Tools suite.
Power Tools 1.3.2 for QlikView
Power Tools 1.3.2 for QlikView - Release Notes
Power Tools 1.3.2 for QlikView - Readme
QlikView 12 compatibility matrix
12.0 | 12.1 | 12.2 | |
QlikView Server Agent | Yes | Yes | Yes |
QlikView Server Super Agent | Yes | Yes | Yes |
QMSClient | Yes | Yes | Yes |
Qv User Manager 10 | Not yet | Not yet | Not yet |
Qv User Manager 11 | Not yet | Not yet | Not yet |
QvsDetector | Yes | Yes | Yes |
QvServerObjectConnector | Not yet | Not yet | Not yet |
ReloadScheduleMigration | No | No | No |
ServerObjectHandler | Yes | Yes | Yes |
ServerObjectHandlerBatch | Yes | Yes | Yes |
SharedFileRepair | Not yet | Not yet | Not yet |
SharedFileViewer | Not yet | Not yet | Not yet |
XMLDBViewer | Yes | Yes | Yes |
Please note that this matrix only describes the compatibility with QlikView 12. It does not indicate whether or not Power Tools are officially supported, because they are not.
SharedFileViewer
Provides insight into the QlikView Server .shared files belonging to .qvw documents. The .shared file stores server data such as server/shared bookmarks, server objects (charts and objects created by a client via the server), annotations and other data that is specific to the document. The tool visualizes the content of the binary .shared file and provides options to "Repair" legacy files, as well as defrag large .shared files.
QvServerObjectConnector
A custom data connector for QlikView that much like the SharedFileViewer provides insight into .shared files, but instead allows to load data straight from .shared files into QlikView documents for analysis. The tool supports extraction of data like ownership, size and type of objects, expressions, annotations, bookmark selections (fields and values) and much more.
QlikView Server Agent
A small service utility that allows easy management of QlikView services. Also allows for quick change of credentials across multiple services at the same time.
QlikView Server Super Agent
A monitoring tool for QlikView Server, that monitors Qv services regardless of version (9+) and reports any service outage with email notifications and/or log files.
QMS API Client
A highly useful tool that gives its user the possibility to interact with the complete setup of QMS API functions in QlikView Server without typing a line of code. Visualizes data structures and enables the user to parameterize and test every function call based on their own data or data from the QMS API.
Qv User Manager
A command-line tool to list, add, remove and edit CAL and user information in a QlikView Server remotely.
QvsDetector
Scans the current subnet for other QlikView Servers and visualizes information like license, root folder, version and such. Also enables certain administrative functions.
Reload Schedule Migration Tool
A small step-by-step instruction tool that helps migrate reload schedules from non-Publisher databases in 9 to Publisher databases in 10 or 11, by manipulating the QVPR database (XML repository required) directly.
Server Object Handler
Enables listing, ownership changing and deletion of server objects in documents hosted on a QlikView Server remotely. Also allows ownership changing to be performed on a selected number of server objects matching a given name pattern.
Server Object Handler Batch
The command-line little brother of the Server Object Handler, that does all the same, but from any command prompt or batch script.
XmlDbViewer
Visualizes and enables searching in and editing of the QlikView Publisher Repository database (QVPR), when in XML format.
Shared File Repair
Uses the upgraded .shared file verify and repair functionality that was re-introduced in QlikView Server (QVS.exe) SR5, by providing a GUI to execute verify/repair operations on one or more .shared files and report the result. Note that even though the verify/repair functionality first came into QVS in 11.20 SR4, this version requires the upgraded functionality in 11.20 SR5.
Q: What is a Power Tool?
A: A Power Tool is a small utility program or application that aid in performing a specific functionality or that extends QlikView functionality in some other way.
Q: When should I use a Power Tool?
A: Power Tools should be used when there is a need for functionality that is not in the QlikView product suite already, to evaluate new functionality or when administrative actions outside that is not available as part of the regular program suite might be needed.
Q: Do I need to install any of the tools?
A: No, all tools are, as of this moment, drop-to-deploy tools. Be aware that some of them require resource files outside of the executable itself, like for example configuration files or DLL files. When deploying, make sure to deploy the whole folder and its content.
Q: Do the tools require administrative privileges?
A: Some of them require this, yes. It is up to each tool and its functionality. If you are using UAC (User Account Control) security, make sure to run the tools in privileged mode (right-click and select “Run as Administrator”).
Q: Is there any documentation for the Power Tools?
A: Yes. A limited documentation for the Power Tools is included in each of the separate tools.
Q: Are the Power Tools Supported?
A: No. The Power Tools are not supported and is seen as use-as-is tools, supplied separate from the QlikView product suite. *** Qlik is not responsible for any consequences such as environmental issues, data corruption, configuration errors or unexpected behavior, resulting from the use of Power Tools. ***
Q: Where do I report bugs in the Power Tools?
A: To report a bug, please visit our QlikCommunity Discussion Forums, for example the QlikView Management or QlikView Deploymentforums or send an email to SupportServiceabilityLabs@qlik.com. Bugs will be fixed between releases of the Power Tools entirely at Qlik's discretion.
Power Tools 1.3.1
Power Tools 1.3.1 for QlikView
Power Tools 1.3.1 for QlikView - Release Notes
Power Tools 1.3.1 for QlikView - Readme
Power Tools 1.3.0
Power Tools 1.3.0 for QlikView
Power Tools 1.3.0 for QlikView - Release Notes
Power Tools 1.3.0 for QlikView - Readme
Power Tools 1.2
Power Tools 1.2 for QlikView - Release Notes
Power Tools 1.2 for QlikView - Readme
Power Tools 1.1
Power Tools 1.1 for QlikView - Release Notes
Power Tools 1.1 for QlikView - Readme
Power Tools 1.0
This article is about converting the GK (Gauss-Krüger) coordinates into WGS 84 (Latitude/Longitude)
Background:
In cartography, the term Gauss–Krüger Geographic coordinate system is named after Carl Friedrich Gauss (1777-1855) and Johann Heinrich Louis Krüger. It is a particular set of transverse Mercator projections (map projections) used in narrow zones in Europe and South America, at least in Germany, Turkey, Austria, Slovenia, Croatia, Macedonia, Finland and Argentina. This Gauss–Krüger system is similar to the universal transverse Mercator system (UTM), but the central meridians of the Gauss–Krüger zones are only 3° apart, as opposed to 6° in UTM. Depending on the zone of the Gauss-Kruger coordinates, different EPSG codes are applicable (The country of Germany is within the EPSG-Codes 31466 to 31469). Nevermind about the EPSG code, the below conversion works on any Gauss-Kruger coordinates.
The typical Gauss-Krüger coordinates is a pair of "Rechtswert" (East) and "Hochwert" (North) coordinates, both numbers are 7 digits long, may have up to 3 decimals (since the unit is meter, decimals don't make much sense as they are about decimeters, centimeters, even millimeter).
Links:
Solution:
Enough about the background, to convert given set of Gauss-Krüger coordinates from a given "Rechtswert" (East) and "Hochwert" (North) in QlikView add the following script fragments. Adjust the values of XCOORD_Field and Y_COORD Field with the effective column names in which the GK values are found in the later LOAD statement.
// constants
LET XCOORD_Field = 'XKOORD';
LET YCOORD_Field = 'YKOORD';
// Formulas
SET @rho = 57.29577951; //=180/PI
SET @e2 = 0.006719219;
SET @b1 = "($(YCOORD_Field)/10000855.7646)";
SET @b2 = "Pow($(@b1),2)";
SET @bf = "325632.08677*$(@b1)*((((((0.00000562025*$(@b2)-0.0000436398)*$(@b2)+0.00022976983)*$(@b2)-0.00113566119)
*$(@b2)+0.00424914906)*$(@b2)-0.00831729565)*$(@b2)+1) / 3600/ $(@rho)";
SET @fa = "(($(XCOORD_Field)-(Floor($(XCOORD_Field)/1000000)*1000000)-500000)/(6398786.849/Sqrt(1+(Pow(COS($(@bf)),2)*$(@e2)))))";
SET @LAT_Formula = "($(@bf)-(Pow($(@fa),2)*(Sin($(@bf))/Cos($(@bf)))*(1+(Pow(COS($(@bf)),2) * $(@e2)))/2)
+(Pow($(@fa),4)*(Sin($(@bf))/Cos($(@bf)))*(5+(3*Pow(Sin($(@bf))/Cos($(@bf)),2))+(6*(Pow(COS($(@bf)),2)
* $(@e2)))-(6*(Pow(COS($(@bf)),2) * $(@e2))*Pow(Sin($(@bf))/Cos($(@bf)),2)))/24)) * $(@rho)";
SET @LON_Formula = "(($(@fa)-(Pow($(@fa),3)*(1+(2*Pow(Sin($(@bf))/Cos($(@bf)),2))+(Pow(COS($(@bf)),2)
* $(@e2)))/6)+(Pow($(@fa),5)*(1+(28*Pow(Sin($(@bf))/Cos($(@bf)),2))+(24*Pow(Sin($(@bf))/Cos($(@bf)),4)))/120))
* $(@rho)/COS($(@bf))) + (Floor($(XCOORD_Field)/1000000)*3)";
Now if you import a file or table with Gauss-Krüger coordinates in fields XKOORD / YKOORD this is your script (dark-blue part). If you plan to use QlikView's built-in Mapping Extension "Quick Map" or "QlikView Mapping Extension", the coordinates-pair needs to go into one field, which I am calling LON_LAT. They need to have US number format. use the dark-red part of the script as well.
SET US_Format = "'','.',' '";
LOAD
*
,Num(LON,$(US_Format)) & ',' & Num(LAT,$(US_Format)) AS LON_LAT
;
LOAD
ID
,XKOORD,
,YKOORD
,$(@LAT_Formula) AS LAT
,$(@LON_Formula) AS LON
FROM
[myExcel.xlsx]
(ooxml, embedded labels);
Enjoy,
Christof
Hi All,
This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.
//to read each file from the specified folder
FOR EACH file in FileList('filepath\*.xlsx');
//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
NEXT i
Next
Hope this helps !!!
please find the attachment for the eg: qvw and test fiels
Regards,
Hi Folks,
If any one interest to freeze all the data by month wise from sql and append it in the qvd to verify the changes per each month. It will help it.
Let vQVDPath = 'D:\';
Let vFreezeTableName = 'MONTH_FREEZE';
LET vNow = Now();
//GFIR
$(vFreezeTableName):
LOAD *,
'$(vNow)' AS MonthFreezeTime,
Floor(Today()) AS MonthFreezeDate,
Floor(MonthEnd(Today(), -1)) AS MonthFreezeDataUptoDate;
SQL Select * from tablename;
//Check the qvd is exists or not
LET vListQVDExists = not isnull(QVDCreateTime('$(vQVDPath)\$(vFreezeTableName).qvd'));
//If exists , concatenate with existing qvds
If($(vListQVDExists)) then
//Find the maximum date
MaxFreezeDate:
LOAD Max(MonthFreezeDate) AS MaxFreezeDateNum Resident $(vFreezeTableName);
//Get the maximum date_num
Let vMaxFreezeNum = Num(Peek('MaxFreezeDateNum',-1, 'MaxFreezeDate'));
//Drop the temporary table
DROP Table MaxFreezeDate;
Concatenate($(vFreezeTableName))
LOAD * From
$(vQVDPath)\$(vFreezeTableName).qvd(qvd)
Where MonthFreezeDate < $(vMaxFreezeNum);
ENDIF;
//Store freeze data into qvd
STORE $(vFreezeTableName) into $(vQVDPath)\$(vFreezeTableName).qvd(qvd);
//Drop the temporary table
DROP Table $(vFreezeTableName);
//Exit script
EXIT Script;
Attached is a QVD file with 249 countries tagged as officially accepted by ISO.
The country names and codes are compatible with for example Google's GeoChart extensions.
Fields included in the QVD are...
- Two letter country code, ISO 3166-1 Alpha2 code
- Three letter country code, ISO 3166-1 Alpha3 code
- Official country name
- Shorter version of country name, more suited for presentation
- Most common regions in the world
Primary ISO code source is ISO.org; ISO 3166-1 decoding table - ISO 3166 Maintenance agency - ISO
The Most vital Set Analysis that we are using in creating our reports. Why we don't make it simple! This document will make it simple and straight forward to everyone. Good luck
Attached is an example of stock aging in SAP. In this example I have used the MSEG (Document Segment) and MKPF (Material Document Header) tables to identify both the current stock position and the purchases (the In movements of stock). The final stock value can then be distributed across the most recent stock in movement records to assign a date as to when this stock was received (based on a FIFO - first in first out methodology). The MKPF table is required as there was an incomplete set of records in the BUDAT (stock-in date) field in the MSEG table in this implementation (this may be different at other sites).
The basic process is as follows:
As this involves a bit of logic to look at preceeding values etc I have attached an example which shows the 4 steps in the coding that you can copy and reuse. The example is based on the QVDs as they would be generated using the SAP Connector script generator** so if you have used this it should be an easy reload of this data.
**Note: the MATNR field is renamed to make it a key in MSEG as [%MATNR_KEY].
Any clarifications or improvements please feel free to add in comments below.
Hello Everyone,
I have attached the document for the important Qlikview functions used in script as well as in UI.
Please have a look and also feel free to update the document or comment in the session for the functions which is missed.
As QlikView applications grow the number of tabs that information is spread across can grow rapidly as well. The new Ajax view makes it easier to navigate when there are many tabs (with the drop down) but if users are using the IE Plugin or an older version of QlikView a lot of the screen can be taken up with tabs.
One way of solving this is to group the tabs into functional areas and place a menu on the welcome tab that allows the user to select which functional area they want to look at.
Furthermore, if some tabs are simply not relevant to some users then it is possible to hide those tabs (and the menu options to show them) from those users.
This document gives an example of a menu that switches tabs on and off and implements hiding of tabs from users based on their OSUser name - loaded from an Inline table in the load script.
I hope you find this document useful, you can find links to other documents I have uploaded here: http://www.quickintelligence.co.uk/qlikview-examples/
Steve
Hi Folks,
This document provide steps to fetch the google spreadsheet information and use in qlikview by using "Qlik Google drive & Spreadsheet connector".
Hope this will help the beginner to use it.
Greetings to all.
I love Qlik, but lately I do not have enough time to keep track of everything that happens.
I have to visit many resources for reading news, articles, tricks, videos.
I set myself the goal of being aware of everything, and spend no more than 5 minutes a day on it.
What can help us with this, of course RSS!
For a long time I tried to find a decent and at the same time a simple program for reading rss's ... and found - QuiteRSS (https://quiterss.org/).
Then I started to compile a list of interesting sites for me and add them to QuiteRSS.
At the moment, there is no particular hierarchy of feed, but I think that it will be needed.
Yes, I am sure that there are other sources of information, but I have not found them.
I propose to work on this issue together and lead an acute list.
Git Repo : https://gitlab.com/bintocher/QlikRSS
Can't attach new file, so link : https://gitlab.com/bintocher/QlikRSS/raw/master/qlik%20docs.opml
Updated 2019.01.22 - now total 53 rss feeds ; updated qlik community rss links
Power Tools for QlikView
Version: 1.2
https://www.dropbox.com/sh/8hac72aljgqlr7d/AAASkzbM2H1OoVcLE_wa0kWGa?dl=0
For the absolute latest version, please see Power Tools for QlikView - One-stop-shop
In this release:
Release notes here: Power Tools 1.2 for QlikView - Release Notes
Readme here: Power Tools 1.2 for QlikView - Readme
Patches
Want to collaborate?
Do you have questions, thoughts or suggestions? Maybe you want to report a defect in the Power Tools? Please post it in the Management forum on QlikCommunity: http://community.qlik.com/community/discussion-forums/management
Also check out our Twitter: @QvPowerTools
DISCLAIMER
Power Tools are a collection of software programs and tools used for troubleshooting purposes only. Power Tools are provided free of charge and are not supported. Power Tools are not official QlikView products and are provided without warranty. Use of Power Tools is entirely at the user's own risk.
Power Tools for QlikView
Version: 1.3.0
Yes, it's actually true! Long story short; we'll be starting to release smaller releases, where we add support for QlikView 12.X continuously in the existing tools. Hence the 1.3.0 version. We'll continue with fixes and move on like 1.3.1, 1.3.2, and so on. Keep an eye out for the next release!
For the absolute latest version, please see Power Tools for QlikView - One-stop-shop
Updated with support for QlikView 12:
Legacy (not yet with QlikView 12 support):
Release notes here: Power Tools 1.3.0 for QlikView - Release Notes
Readme here: Power Tools 1.3.0 for QlikView - Readme
Want to collaborate?
Do you have questions, thoughts, suggestions or bugs to report on Power Tools? Please post in the forums on QlikCommunity: https://community.qlik.com/community/qlikview/management
Also check out our (currently dead, soon to be revived) Twitter: @QvPowerTools
DISCLAIMER
Power Tools are a collection of software programs and tools used for troubleshooting purposes only. Power Tools are provided free of charge and are not supported. Power Tools are not official QlikView products and are provided without warranty. Use of Power Tools is entirely at the user's own risk.
This VBS script shows how to combine report files in QV Server Audit.log with Tasklog.txt entries of Distribution Server
Example: Automatically restart qvw reports in QMC console (with edx-trigger) when no activity on file has been detected for 5 minutes
Regards - Marcel
There is a large amount of useful information that is stored in the header of QVD files. This gives information about the data in the file, when it was created and which app created it. This data is in an XML format and can be viewed in a text editor.
Being XML it can also be loaded in to QlikView and viewed there.
This blog post describes how this can be done and gives some background to the application uploaded here:
https://www.quickintelligence.co.uk/qlik-qvd-xml-header-viewer
The application enumerates around a number of libraries and reads the header information from each QVD in those libraries. It persists the information it finds to a QVD and also creates a timestamped archive - so you can see how your QVDs are changing over time. By default this is written to the same folder you are querying QVDs from.
If you have any questions regarding getting this app set up please post in the comments below.
There is a QlikView version of this application also, which you can find here:
Qlik Sense App: QVD Meta Data Viewer
Both versions of the app also include our popular Data Profiler, which you can find more information about here:
Qlik Sense App: Generic Data Profiler
I hope that you find the application useful. You will find other applications that I have uploaded under my profile on QlikCommunity or on our Downloads page..
Steve
When I create a dashboard to mantain a good layout and to offer a good user experience always I need to set the same color for the same dimension value in each chart.
Unfortunally there isnt' a easy way to do this, and often a solution is to create some additional fields (R,G,B) for each dimension.
I don't like to have dozens of fields in my model with the problem to naming them.
To turn around this problem, I writed a single expression in a variable that doing all for me and now I use it always.
Let's see if you like this method too.
The idea is to use the functions Pick(Match(...)) to realize the magic.
I use QlikView Test Script and Qlikview Components library to realize an example. Test Script create the dimensions Dim1, Dim2, Dim3 and the expressions.
For Dim1 the values are A, B, C
For Dim2 the values are a, b, c, d, e, f
For Dim3 the values are X, Y, Z
For each values in dimensions need to set a variable with the associate color, to do easy this I use ColorTheme.xlsm file and use Qvc.ColorTheme to read it.
Now the best point... our function Pick(Match(...)), that I called App.Color.GetForField and set the value in Settings.xlsx.
Here the value:
=Pick(Match(Only($(=$1)), ' & Chr(39) & Concat({1} DISTINCT $(=$1), Chr(39) & Chr(44) & Chr(39)) & Chr(39) & '), ' & PurgeChar( Replace( 'App.Color.v.Dimension.$(=$1).' & Concat({1} DISTINCT $(=$1), chr(10) & ',App.Color.v.Dimension.$(=$1).') , ' ', '_'), '[]') & ')'
To use it in a chart write $(=$(App.Color.GetForField('[Dim1]'))) in the expression's backgroud property
The values in specificated dimension get automatically choosed color.
I hope you like this.
Regards
Luca Jonathan Panetta
I developed the QlikView Data Analyzer Application with features to do Data profiling, Rapidly analyse and validate the data in the Data model of a QlikView Application. I have added Macros & Buttons to the layout to quickly create List boxes and Table boxes based on the values selected in the Field Name and Table Name list boxes. There is also a button to create empty Text Boxes for quickly writing any expressions while analysing and validating the data. Once the analysis is completed the layout can be cleaned up by clicking on the respective Delete Object buttons.
All the objects in the Data Analyzer Tab can be copied into any QlikView Application.
Follow these steps for copying the objects into any QlikView Application:
Select only those fields that are required in the Table Box Feature
In the above example, the Address Table contains 9 fields, of which only 3 fields were selected and when clicked on Create Table Box Button, it will generate a Table box with only 3 fields that were selected. This feature helps to load only few required fields from a large table. Multiple field names can also be selected by holding CTRL Button and selecting them in the Field Name list box.
Script to Load All the Tables in a SQL Server Database
Also in the Script, I wrote the code that can be used to connect to any SQL Server Database and load all the tables in that Database and generate QVDs. The QlikView Analyzer functionality can be used to quickly do data profiling and analysis of the data in that Database. For this application I used Microsoft SQL Server Adventure works database.
I have developed the QlikView Data Analyzer Application with inspiration from the Generic Data Profiler App built by Steve Dark,
Thanks Steve
Note 1: This macro code creates Two variables, to enable creating list boxes and Table boxes dynamically:
So please avoid using these two variable names for any other purposes in your App when incorporating Data Analyzer functionality.
Note 2: As most of the times, macros are not advised for the production environments, its better to use the Data Analyzer functionality only in the QlikView Applications in the development environment and not to incorporate in the production applications
Sandeep Vangala
Founder & CEO
SANRIDGE LTD
London, UK
Security in any context is critically important. It becomes especially important
when applied to the enterprise software solutions that organizations rely
upon to make decisions based on sensitive information. Ensuring that the
right people have access to the right information at the right time and no
one else is a critical part of being able to support decision making. Company
performance, employee payroll, sales data, personally identifable information
and forecast information are just some examples of company information that
are commonly represented within QlikView applications.
Security in QlikView is multi-faceted and powerful but is also easy to
implement and familiar to IT professionals. This paper will discuss the
following topics as applied to QlikView:
• Authentication. Who are you? How did you prove it?
• Authorization. What are you allowed to see? What are you allowed to do?
• Implementation. How do you implement security in QlikView?