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:

• An online translater example is here:  WGS84 L�nge/Breite oder Gau�-Kr�ger nach MGRS :: Geograph Deutschland
  It supports even http-get requests.
• I would not have been able to solve this without this How-To Excel. http://www.kram.de/pca/koordinatenumrechnung.xls Thanks to the author!
• http://en.wikipedia.org/wiki/Gauss%E2%80%93Kr%C3%BCger_coordinate_system
• DE:Gauß-Krüger - OpenStreetMap Wiki (in German)

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,

Avinashelite‌

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;

          How To / Missing Manual

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

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.

It's as easy as that.

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:

1. identify the purchase instances (headers) from the MKPF table. This is required to get the stock-in date for each purchase instance (BUDAT).
2. 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').
3. 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.
4. 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.

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

http://www.quickintelligence.co.uk/qlikview-blog/

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:

Product	Payment

The Qualify statement will assign name of the Table to fields:

Remember

• 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, embedded labels, table is Product);

QUALIFY *;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Payment);

The Outcome:

Table Viewer:

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:

QUALIFY Category,
Value;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is Product);

QUALIFY Category,
Value;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is 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, embedded labels, table is Product);

QUALIFY *;
UNQUALIFY [Serial No];

Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(ooxml, embedded labels, table is 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)

Outcome is as below:

Fields:

Table Viewer:

Feeling Qlikngry?

How To /Missing Manual(18 articles)

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.

Do you have a feature request or a bug?

• Start a discussion in the QlikView Management or Deployment forums, or
• Email sbn@qlik.com

Latest release

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

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.

Frequently Asked Questions

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.

Older versions

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

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

Power Tools 1.1 for QlikView - Release Notes

Power Tools 1.1 for QlikView - Readme

Power Tools 1.0

Power Tools 1.0 for QlikView

Power Tools 1.0 for QlikView - Readme

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

Download here

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:

• QlikView Server Agent *updated* - 1.6.2
• QlikView Server Super Agent *updated* - 1.7.2
• QMS API Client - 1.0
• Qvs Detector - 0.9
• Qv User Manager *updated* - 1.0.1
• Reload Schedule Migration - 1.0.4321
• Server Object Handler *updated* - 2.0 - Updated to 2.1 @ 2014-03-18 11:00
• Server Object Handler Batch *updated* - 2.0
• Shared File Viewer *updated* - 1.5.2 - Updated to 1.5.3 @ 2014-02-21 09:32
• Qv Server Object Connector *updated* - 1.5
• XML Db Viewer - 1.0.4321
• Shared File Repair *new* and *updated* - 1.1 - Updated to 1.2 @ 2014-06-30 21:29

Release notes here: Power Tools 1.2 for QlikView - Release Notes

Readme here: Power Tools 1.2 for QlikView - Readme

Patches

• Shared File Viewer: Fixed broken "Color on top" sort option, new version 1.5.3
• Server Object Handler: Fixed credentials being passed wrong, causing no documents to show in list, new version 2.1
• Shared File Repair: Fixed "The path is not of a legal form" error when selecting Qvs binary, added "Don't show this" option in startup notification, fixed fonts", new version 1.2

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:

• Server Object Handler *updated* - 2.2
• Server Object Handler Batch - 2.0 - Already had legacy support

Legacy (not yet with QlikView 12 support):

• QlikView Server Agent - 1.6.2
• QlikView Server Super Agent - 1.7.2
• QMS API Client - 1.0
• Qvs Detector - 0.9
• Qv User Manager - 1.0.1
• Reload Schedule Migration - 1.0.4321
• Shared File Viewer - 1.5.3
• Qv Server Object Connector *updated* - 1.5.1
• XML Db Viewer - 1.0.4321
• Shared File Repair - 1.2

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

http://www.quickintelligence.co.uk/qlikview-blog/

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