This article is about converting the GK (Gauss-Krüger) coordinates into WGS 84 (Latitude/Longitude)Background:In cartography, the term Gauss–Krüger Ge...
This article is about converting the GK (Gauss-Krüger) coordinates into WGS 84 (Latitude/Longitude)
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).
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.
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)
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 = "'','.',' '";
,Num(LON,$(US_Format)) & ',' & Num(LAT,$(US_Format)) AS LON_LAT
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 ...
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
Enter the full file path and name for the QVW
e.g. C:\Demo\Application\Executive Dashboard.qvw
Step 2 - Enter the destination folder path where the extract data will be saved
Enter the full folder path for the destination folder
e.g. C:\Demo\Export Data
Step 3 - Select the output format type
Your choices are either QVD (Qlik's proprietary file store format) or more generic CSV
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.
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 id...
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:
identify the purchase instances (headers) from the MKPF table. This is required to get the stock-in date for each purchase instance (BUDAT).
Join in the purchase records (where SHKZG = 'S' to identify in movements of stock) and the unit cost of these movements by material (MATNR) and location (LGORT). The MSEG and MKPF tables join based on the MANDT, MBLNR and MJAHR fields. I have chosen to right join the MSEG table as I only require the purchase records (i.e. SHKZG = 'S').
Calculate the latest final stock values for each Material (MATNR) and Location (LGORT) and add this against each purchase line. This does repeat the final stock value across many lines but that is used in the next step.
Calculate the distribution of the current stock over the most recent preceding purchases. I have also calculated the cost of the stock based on the unit value of the final stock as calculated in step 3.
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.
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 na...
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.
What it is used for?The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a pre...
What it is used for?
The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a prefix.’
In situation where we have two (or more) Tables with the same field names:
The Qualify statement will assign name of the Table to fields:
To use the script below the Excel file must be saved in the same folder that your qvd file
Otherwise you will need to make changes the this path - [Qualify.xlsx]
QUALIFY *; Product: LOAD[Serial No], Category, Value FROM Qualify.xlsx (ooxml, embeddedlabels, tableis Product);
QUALIFY *; Payment: LOAD[Serial No], Category, Value FROM Qualify.xlsx (ooxml, embeddedlabels, tableis Payment);
Read only if you need to know more....
If we have not used ‘QUALIFY’ statement in situation as above Qlikview would map both of the Tables with outcome like below:
The end result -merge of those two Tables is correct only for the “Serial No” fields
The “Value” and “Category” fields although merged is none of use to us.
To fix this we can only Qualify fields that we do not want to associated:
QUALIFYCategory, Value; Product: LOAD[Serial No], Category, Value FROM Qualify.xlsx (ooxml, embeddedlabels, tableis Product);
QUALIFYCategory, Value; Payment: LOAD[Serial No], Category, Value FROM Qualify.xlsx (ooxml, embeddedlabels, tableis Payment);
or by using UNQUALIFY statement:
(which works as opposite to QUALIFY)
QUALIFY *; UNQUALIFY[Serial No]; Product: LOAD[Serial No], Category, Value FROM Qualify.xlsx (ooxml, embeddedlabels, tableis Product);
QUALIFY *; UNQUALIFY[Serial No];
Payment: LOAD[Serial No], Category, Value FROM Qualify.xlsx (ooxml, embeddedlabels, tableis Payment);
In second example the ‘QUALIFY *’ part will add Table name to all fields and the UNQUALIFY statement will remove those names only from specified fields(this method is very useful if we got large numbers of fields)
Q-Eye is an easy to use QVD files editor that works with both QVD and QVX files It can export data as excel text and sql filesIn this release we conce...
Q-Eye is an easy to use QVD files editor that works with both QVD and QVX files It can export data as excel text and sql files
In this release we concentrated on making the life of end users easier.
The most import change is how metadata statistics is presented:
It shows Total rows, Unique Values, Uniqueness, Null Count, Bit width, Size bytes ,XML type, Comments and Tags
Next important change is introduction of Field Metadata dialogue.
The rest of the changes are:
Restructured Ribbon toolbar
Double click on the header renames field
Metadata dialogue's xml and file tabs are only shown to not yet modified documents
Generate LOAD Statement button was added to Metadata dialogue.
Ability to edit "generated load statement" was introduced
Added option to set number of rows
Added option to set number of columns
Added shortcut to insert row
Added shortcut to delete row
Added shortcut to insert column
Added shortcut to delete column
Improved column drag and drop functionality
Format button was renamed to fit to content
Software automatically adds rows when necessary
Lots of small but useful bugs fixes
Download Q-Eye Now
Note: Q-Eye is not on Qlik Market despite the fact that number of people and companies promised us to help with this matter. We believe that eventually it will happen. Meantime we will continue making it better.
This example demonstrates the SQL WriteBack capability of QlikView via the use of an Extension object. It can be useful for passing a filtered subset ...
This example demonstrates the SQL WriteBack capability of QlikView via the use of an Extension object. It can be useful for passing a filtered subset of data to a database, writing a comment to a record or simply amend/correct something in the database. Since there’s no open source code available I decided to write this example in my free time to make it available for everyone just like me trying to achieve a writing back functionality to a database. Hopefully this might be useful to some.
Please note that this example was created to be as simple as possible and does not serve to fill in any business necessities. It should rather demonstrate that it works and provide the code for free to anyone who wants to extent it to his own business needs.
A detailed installation guide is included in the .zip file.
I will try to support and answer as many questions as possible in the comments.
Often when building QlikView applications, or picking up applications which have been built by someone else I want to have a quick and easy way of vie...
Often when building QlikView applications, or picking up applications which have been built by someone else I want to have a quick and easy way of viewing the data that is in that application. To enable me to do this I have put together a page of objects that lists all tables and fields in the data model and then gives outline information about any selected field. These objects can be copied and pasted into any QlikView application to view the data model of that document.
I have documented how this document works and the reasons why you might use it in a blog post here:
PLEASE NOTE: The 'With Mask' version of the file includes an experimental tab that may or may not work well on large data sets. Please see comment below for details. If you are not sure which to download go for DataProfiler.qvw. Thanks!
Hi, all of my Qlik applications have QVD data staged and prepared in advance. Here I will go through how I import them efficiently into QlikView.For n...
Hi, all of my Qlik applications have QVD data staged and prepared in advance. Here I will go through how I import them efficiently into QlikView.
For now I’ll assume you’re extracting your data each day and incrementally storing them to monthly QVD files.
Lets say the requirement for the dashboard is to have the last two months of complete data plus the current month to date. So if today were the 15th of June 2014 the Dashboard should contain data from 1st April to 15th June 2014.
You could load the data like this (replacing the dates to min / max variables and use dollar expansion):
Issues with this approach:
We’re using a wildcard on the QVD filename so we’re attempting to load from files we know there’s no corresponding dates.
The where clause will also prevent the QVDs being an optimized load.
The resulting table has all the %KeyDate(s) for the time period in numeric format. You need to have an identical field in the FACT data as we’ll be using this field in the where exists statement.
We use the QVDLoadSuffix field in the next load statement
2) Load a Curser Table containing QVD Names
Here I create a new table with distinct values
This tell us the QVD files we need to look at when loading the data
3) Looping through the curser table
Here we perform a loop reading each row of ‘QVD_Files_To_Load’ (in this example it had three rows as shown earlier; 2014-04, 2014-05 and 2014-06)
The Field Value ‘QVDLoadSuffix’ is read into a variable and that is used within the filename of the QVD we’re loading from.
The where exists ensures only those %KeyDates already loaded (in the first step) are brought back. You can argue on this example the where clause is not required and you’d be correct. I’ve added it to show how we tackle both of the original challenges and it certainly would be required if you were loading the last 90 days (three months) from the current date.
It’s worth noting you can only load optimized with one where exits clause. You can’t use “where exists(field1) and exists(Field2)” without loosing performance although you could combine the fields in your data.
I’ve assumed the Fact Data has the same fields and therefore it will be automatically concatenated. If your data isn’t consistent you could create an inline or autogenerate a table with the complete field list and one row of null values before entering the loop and then concatenate to that. That will also ensure your loads stay optimized (There a useful post which talks about optimization - http://community.qlik.com/docs/DOC-2641)
You can also pass from the QMC a variable that tells the application how many dates to load in Step 1. For example you may want to create a light version with only three days worth of data. Putting Step 1 within a conditional IF statement you can very simply control the load behavior. (I’ll post more about this technique soon)
If you’re a QlikView developer, you probably know that there are a few tasks that can be performed with QlikView from the command line. One of my favo...
If you’re a QlikView developer, you probably know that there are a few tasks that can be performed with QlikView from the command line. One of my favorites is the Open without data option (see below to read how it can sometimes be a real life saver). As this (and some others) is a really useful option, I think it should be more readily available to be used when needed without having to launch a cmd window or even create a .bat file. So, I created a custom context menu for QlikView files that puts these options just one click away (literally). Here is the result:
I will first introduce these options and why they can be useful. At the end of this post, you’ll find instructions to add this context menu to your own system.
Opening a QlikView document without data
Besides the command line, there’s another way to open a document without its data: simply right click on the file from the list of Recently Opened Documents and select the corresponding option.
If you’re a developer, you’d normally use this option when reviewing script, expressions, or general layout of a QlikView document and don’t need to see the data contained in it. This is particularly useful if we’re talking about files that, when opened normally, would eat up a considerable amount of RAM in your computer.
The Open Without Data option is sometimes a life saver as well; and I can talk from experience. Once, I was about to present a demo we’ve made for a prospective customer and, just before the meeting started, I powered up my laptop and tried to open the file only to see the following message appear:
At some point during the last changes applied to the document, the file had became corrupt, we had no backups and everybody was there waiting to see the demo. Maybe just in a moment of luck, right before panicking, I right clicked and opened the document without data. My heart was beating again. After that, just a quick reload and we were off demoing QlikView again.
The command line, one qlik away.
But is this option only available from the Recently Opened Documents? What happens if you want to open without data a document that has not been opened before on your machine? As mentioned previously, there’s another option: the command line.
By using the command line, we can run QlikView and perform some common tasks, among which is the “Open Without Data” option. Just call qv.exe from the cmd window, specify a file to be opened, and add the /NoData switch. Other options include the ability to open and reload a qvw file, open a document overriding module security, etc.
All these options exist. However, in my opinion, these options should be more readily available, so that they can be easily used when needed. Enter RightQlik.
RightQlik is a simple custom context menu that’s shown when right clicking on a QVW file. This allows quick access to some common functions that a developer would often perform on a QlikView file:
Open in a new QV instance: This option will open the selected file in a new QlikView instance. This allows for quick switching between several QlikView files, opened in several windows.
Open without data: opens the document without also loading its data.
Reload document: Runs a reload of the QlikView script and closes the document. With this option, you can run simultaneous reloads of several QlikView documents with a simple click by first selecting all of them.
Reload and keep open: Runs a reload of the QlikView script and the document remains open after finished.
This custom context menu is only shown when dealing with QVW files, and will stay out of your way when dealing with any other file type.
To install, simply run the following exe file with administrative privileges and that’s it! The installation file is prepared to work on both 32-bit and 64-bit systems, and has been tested in several versions of Windows.