Direct Discovery in QLIKVIEW 11.2The biggest limitation of QLIKVIEW in data point of view, is that all the data should be stored in RAM. As big data i...
Direct Discovery in QLIKVIEW 11.2
The biggest limitation of QLIKVIEW in data point of view, is that all the data should be stored in RAM. As big data introduction data is too huge, so QLIKVIEW introduce a hybrid approach. In which QLIKVIEW stores the dimension in the RAM, while measures or the numeric values are stored in the database you want.
Basically aggregation of measures are run on the database not in memory, which reduce the RAM usage. It will be used when our database speed is good enough.
QLIKVIEW introduce the new type of query for these kind of requirement “Direct Query”.
It has three types of field
1) Dimension:- which are used for association
2) Measure: - on which we perform the aggregation
3) Detail:- those field which can’t be used for association or aggregation. Like Comments.
The script look like below
The unique values of Dimension will be fetched in the QVW, and that can be used for creating association between data and other data.
This QlikView Document will help you to understand QlikView Joins, Keeps, Concatenate and Applymap functions with examples and scripts. This is a Nice...
This QlikView Document will help you to understand QlikView Joins, Keeps, Concatenate and Applymap functions with examples and scripts. This is a Nice QlikView Training application to perform a QlikView Training by using a QlikView application itself.
Hi, Everyone!Today I'm gonna tell you one of the QV mysteries - creating a vertical chart with less/more bars in a straight table.So, if you are inter...
Today I'm gonna tell you one of the QV mysteries - creating a vertical chart with less/more bars in a straight table.
So, if you are interesting with something like this:
Please, make an effort to read the text below.
For instance you have a data:
LOAD * Inline
1. Load it and create a Straight Table with Task dimension;
2. Create an expressions "less" and "more" which will sum negative and positive values of Val field. Use the Sum of Rows in Total Mode to show a partial sum:
3. Create one more expression and change the display option from "Text" to "Linear Gauge" and set the parameters of it:
So, you need to create two segments with white color and set an expression of minimum value in Min Value and Lower Bound of the first segment. Switch off the Autowidth Segments. Hide boundaries and outlines.
4. Now, here is a magic. We will create a red bars not with a segment setup but with a background of cells of our new expression.
Go to the new expression and find a background color option. Set an expression which will color our cells in red if they are under the zero.
The picture above is for paragraphs 3 and 4.
If you did it right you will see a red background overlapped with white segment. Red bars completed.
5. Green bars are easier, there is no need of background color. It will be enough to create two segments from 0 to max and set a correct colors:
Note: Red bars and Green bars are two separated expressions with adjacent zero line.
6. The last one is to hide borders and make some cosmetical things:
That's it. Straight-Vertical-Chart is completed. Yes, there is a little defect when your negative value will be the last of the table (red thin background line will be visible through cell). But it shouldn't make a big sense.
Hope you'll find it usefull and may be it will save your time!
If you like the post - please rate it or leave a comment!
Hi, everyone!Today I want to share with you with some tricks which you may use to improve your projects/dashboards from visual side.The main idea of t...
Today I want to share with you with some tricks which you may use to improve your projects/dashboards from visual side.
The main idea of this post is using a function RangeSum() to achieve a different results which can be used to define a colors in your charts.
In the attached file you will find a four charts with different results of the same thing:
The first one gives an answer for the question "Is the next bar is bigger than previous or not?"
The second shows a dynamic and mark plot values with symbols ▲/▼ (alt+30, alt+31).
The third shows a number of bars that bigger than average among all values.
And the last one tells a user how good or bad a business are going. It accumulate a difference between adjacent bars and at the end we can see a total difference. If it's uder the zero the plot area will be red, else - green.
Also, you can be interested with the 2nd chart because it's a combined of two similar charts, but with the different visualization.
Note: often use of function rangesum() may reduce a performance of your project! Don't get carried away 😃
Also you can find how to use a refference lines.
I will be pleased if this material will help you even a little!
I was thinking about how to segment the employees of the company according to their age and I found the "Class" function. It is very interesting and v...
I was thinking about how to segment the employees of the company according to their age and I found the "Class" function. It is very interesting and very comfortable when the intervals are regular, 5 in 5 or 10 in 10, for example. But when the intervals are different from each other, it does not work well for us.
I think a good solution, which seems to work correctly and is very flexible, is to use the "IntervalMatch" function. It behaves similarly to the Excel Vlookup function.
The magic is in this function that manages to make a relation based on the definition of the intervals.
Load * Inline [
// Load intervals or ranges
Load * Inline [
Start, End, Group
18, 20, Young
21, 25, AlmostYoung
// The magic is in this function that manages to make a relation based on the definition of the intervals.
Under Document Properties there is a Tables page that offers functionality surrounding QlikView metadata. The attached document explains how to use so...
Under Document Properties there is a Tables page that offers functionality surrounding QlikView metadata. The attached document explains how to use some of the functions on that page such as Comments and Field Tags. We will see where QlikView displays this information. As Developers, we all relish writing documentation.......sure. Thankfully, some elements in QlikView will allow you to get a jump start in creating documentation that you should maintain for your QlikView application.
Hi everybody,I've been demanded recently customizable reports in Qlikview 11 and a way to accomplish this requirement was to let the users explore and...
I've been demanded recently customizable reports in Qlikview 11 and a way to accomplish this requirement was to let the users explore and design their own reports by adding or hiding columns and expressions.
I just wanted to share with everybody a simple example in which by loading inle sample dummy data we can create an small testing table and a chart which is using this Qlikview feature. Let's review it step by step :
1. Create an inline sample table with 3 or 4 dimensions and a couple of expressions:
2. Let's create now 2 data island in our model, those with contain all dimension names and expression names each data island.
3. Now we have all the data model built in our QVW. The idea here is to create a straight table containig the dimensions and expressions of the Data table and let the user select which ones will be displayed. Once we have the table created with our dimensions and our expressions we will need to put the following conditions:
Qlikview is E-TL offline (Transform, Load) tool like every standard BI tool, but with a lot flexible features that can make the using as you wish. ...
Qlikview is E-TL offline (Transform, Load) tool like every standard BI tool, but with a lot flexible features that can make the using as you wish.
First, let’s talk about limitation.
QVW file include all the information: Data and design setting, actually It’s a “finished product”.
we can use Binary function, control and reorganized the data in the “Transform” and “Load” part.
When we finished all the process and arrived to the last part we can’t make any changes and probably not online
Up to version 11 the dynamic update feature get in
Dynamic update its feature that give as make query on virtual data model that floating on memory and show changes imminently
So what will upend after the reloading? all the changes will reset, the database not yet update.
To solve this issue that give us real time system we most to use integration of two component.
1) Vbscript for SQL query to the Data Base. (Data Base Update)
2) Dynamic Update query to the qvw model. (Qlikview Update)
Dynamic update setting
1) Server setting:
2) Qvw setting:
3) Vbscript editor setting:
4) VBSCRIPT MACRO LOOP UPDATE FROM TEMP TABLE:
Rem ***set 3 variable for every field in Temp Table**** V1 = 0 V2 = 0 V3 = 0
Rem ****SET ODBC CONNECTION SETTING*** set conn = createobject("adodb.connection") Set objRecordSet = CreateObject("ADODB.Recordset") conn.open "dsn=DB_QV;uid=;password=;"
Rem ****Get Table From SheetDocument**** set TableBox = ActiveDocument.GetSheetObject("TB4UP") CellRect = ActiveDocument.GetApplication().GetEmptyRect() CellRect.Top = 0 CellRect.Left = 0 CellRect.Width = TableBox.GetColumnCount CellRect.Height = TableBox.GetRowCount set CellMatrix = TableBox.GetCells( CellRect )
Rem ****Loop for Insert 3 variable for every Line***** for RowIter=CellRect.Top+1 to CellRect.Height-1 V1 = (CellMatrix(RowIter)(0)).Text V2 = (CellMatrix(RowIter)(1)).Text V3 = (CellMatrix(RowIter)(2)).Text
SQL_String="UPDATE * SET FIELD1 = '1' WHERE FIELD2 = "&V1&";"
Rem **** Run Data Base server Query
objRecordSet.Open "UPDATE TABLE1 SET FIELD1 ='1' WHERE FIELD2 ="&V1&";",conn
Rem **** Run Dynamic Update
SET Result = ActiveDocument.DynamicUpdateCommand (SQL_String) next
Rem *** clear temp table
SET Result1 = ActiveDocument.DynamicUpdateCommand ("DELETE FROM UPDATE_DEBUG WHERE -1")
When running Server Publisher classes I often get the question if it is possible to edit a task or document category name or even remove a category n...
When running Server Publisher classes I often get the question if it is possible to edit a task or document category name or even remove a category name once this has been created.
As far as I know this is not possible to do from within the actual QMC however this is possible if you edit the name directly in the QVPR. I am always cautious when touching the QVPR and always make sure I have backed this up before actually making any changes (just look for the Repository tab under Systems > Setup > Management Service and press the Backup button at the bottom of the page). To edit the actual QVPR (XML tables) I use the Power Tool XMLDBViewer. This allows me to connect to the XML table called Category where I can Edit, Delete or even create new Category's directly in the XML table.
Let me know if you have any additional questions about this.
Hi,I did an application where I show you about use of the APIGoogle Maps. I did a slider where you choice a distance for to find his regions and his c...
I did an application where I show you about use of the APIGoogle Maps. I did a slider where you choice a distance for to find his regions and his costomers. Very easy, but for me was very helpful. I hope it is helpful for you too!
Hi,I did an application where I show some actions that you could need in his Qlik applications. This application contains automation by macros writing...
I did an application where I show some actions that you could need in his Qlik applications. This application contains automation by macros writing in VB Script that allow changes in Chart such as font size, font color, also I can to hide legend, dimensions, values on data points etc.