QlikView documentation and resources.
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,
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.
Hi,
I've built a document extension that allows you to close the ajax session when closing the browser or browser tab.
There's another document extension in the forum that never worked for me that's why I put this new document extension together.
You can download the extension from here or my blog. (the latter will always be the most updated version)
JV
Inter-Record Functions:
These functions are used when a value from previously loaded records of data is needed for the evaluation of the current record.
For Example ,will Take Sample Table and Perform all the Inter - Record Functions ,
Table: LOAD * INLINE [ Variant, Customer, Sales S1, Wilson, 12 S2, Johnny, 55 S3, Jenny, 6 S4, Mary, 4 S4, Rob, 33 S5, Dave, 44 ];
(Copy and paste below code into the edit script window and reload then at Front end
remember to sort dimension and expression by load order)
1.Field Functions:
A) Field Index :
Syntax: fieldindex( fieldname, value )
e.g. the field name must be enclosed by single quotes.
This is like vLookup.
Example:
From this above table Field Index ,
FieldIndex('Customer','Jenny')
Output:
B) Field Value :
Syntax: fieldvalue( fieldname, n )
Example:
From this above table Field value ,
FieldValue('Customer',2)
Output:
C) Field Value Count:
Syntax: fieldvaluecount ( fieldname )
Example:
From this above table Field value Count ,
FieldValueCount('Customer')
Output:
2.Above Function:
Syntax: above( [ total ] expression [, offset [,n ]] )
From the Above Data Following will be the Outputs,
Sum(Sales):
Above(Sales):
3.Below Function:
Syntax: below( [ total ] expression [, offset [,n ]] )
From the Above Data Following will be the Outputs,
Sum(Sales):
Below(Sales):
4.Before Function:
Syntax: before( [ total ] expression [, offset [,n ]] )
Sum(Sales):
Before(Sales):
5.After Function:
Syntax: after( [ total ] expression [, offset [,n ]] )
Sum(Sales):
Before(Sales):
6.First Function:
Syntax: first( [ total ] expression [, offset [,n]] )
Sum(Sales):
First(Sales):
7.Last Function:
Syntax: last( [ total ] expression [, offset [,n]] )
Sum(Sales):
Last(Sales):
8.Rowno Function:
Syntax: RowNo( [ total ] )
Rowno():
9.No Of Rows Function:
Syntax: NoOfRows( [ total ] )
NoOfRows():
10.Column No Function:
Syntax: ColumnNo( [ total ] )
Column No():
11.No Of Columns Function:
Syntax: NoOfColumns( [ total ] )
No of Columns()
12.Dimensionality() Function:
Syntax: dimensionality ( )
Example:
For a pivot table with three dimension columns to the left the following would be returned:
3 for all ordinary data cells
2 for 1st level partial sums and entries not expanded in the 3rd column
1st for 2nd level partial and entries only expanded in the 1st column.
0 for grand total cell.
Dimensionality():
Thus,These all above Functions will Come under Inter Record Functions Particularly at front end.
Please Find the Attached Complete(Inter-Record Functions.QVW) Application.
Hope this Helps in understanding these functions,
Thanks & Best Regards,
Hirish.V
Have you ever wondered how the examples from the Qlikview help may look like?
Please see below and enjoy responsibly...
Peek()
not a boo...
This function belongs to to Inter-Record functions and can only be used in the script (back-end).
Description(Qlikview Help)
Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.
Fieldname must be given as a string (e.g. a quoted literal).
Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.
If no row is stated, -1 is assumed.
Tablename is a table label without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.
Syntax:
Field_name - is a name of of your field(column)
row_no - the row from which the data is returned
(0 is first row
1 is second row
and so on..
-1 is the last row)
table_name - a name of table from where the data are fetched
Returns values from previous row or row specified by the row-no argument.
This is true only if we use this function to create variable (please see below examples)
Data Model
(Copy and Pasted below code into Edit Script window and reload)
Tab1:
load
peek(Sales) as S1,
peek( Sales,2 ) as S2,
peek( Sales,-2 ) as S3,
if(rowno()=1,Sales,Peek(RunnigTotal,-1)+Sales) as RunnigTotal,
Previous(Sales) as PSales,
numsum( Sales, peek( 'Sales' ) ) as Bsum,
Sales
inline [
Sales
100
200
300
400
]
;
load
peek( Sales, 0, 'Tab1' )as S4
resident Tab1;
LET vpeek= peek( 'Sales', -1, 'Tab1' ) ;
Please see below swuehl comments about difference when you use negative or positive numbers as second argument.
Is worth to mention that Peek() is reading from Output table(opposite to Previous() function which reads from Input Table.
Example:
OutputTable:
LOAD field
RESIDENT InputTable;
source:Difference between peek() and previous() funcation
Example 1
In this example as we did not specify the row_no argument, all but last rows are returned.
peek( 'Sales' )
peek( 'Sales' ) = peek( 'Sales',-1)
Example 2
IF the row_no argument is added only ONE value is returned
peek( 'Sales',2 ) Remember 2 = third row
Example 3
If we use negatives numbers as row_no the return value is our initial table minus number of rows specified by the second argument.
peek( 'Sales',-2 )
As you see the last 2 rows have been removed from the table.
Example 4
The below syntax is used when we want to return value from external table(see data model)
peek( 'Sales', 0, 'Tab1' ) as S4
Example 5
Each row is a SUM of current row + one row above.
numsum( Sales, peek( 'Sales' ) ) as Bsum
Bonus
How to store value into variable:
With below example
LET vpeek= peek( 'Sales', -1, 'Tab1' ) ;
we will store value of 400 in variable vpeek
and then use in front end development.
Bonus 2
As previously stated we can use Peek to return values from field that was not been yet created.
How does it wok?
In our data model we have this line:
if(rowno()=1,Sales,Peek(RunnigTotal,-1)+Sales) as RunnigTotal,
Although RuningTotal has not yet been loaded we can return the values from that line:
Still feeling Qlikngry?
Note:
Any string after an Aggregation formula Sum(), Count(), Min(), Max() where you typically would put a set modifier, is simply ignored when the SQL command is computed. So if LineTotal is a Direct measure field and you use Sum({1} LineTotal) the SQL will only contain Sum(LineTotal).
Note: Instead of the If( ... = ...) equal match you may also use LIKE operator or functions as "Match()" or "WildMatch()".
Test | Formula | Result | Why? |
---|---|---|---|
1 | Sum(If(Territory.Name = 'Germany', LineTotal)) | Failes | The underlying SQL database does not understand the inline-IF syntax of QlikView |
2 | Sum(CASE WHEN Territory.Name = 'Germany' THEN LineTotal ELSE 0 END) | Failes | Even when this is correct T-SQL syntax, QlikView doesn't allow native SQL and wouldn't send this |
3 | If(Territory.Name='Germany', Sum(LineTotal)) | Works if .... | .... works if the Dimensionality is Territory.Name. Does not work for totals and not if the dimensionality is different |
4 | Aggr( If(Territory.Name='Germany', Sum(LineTotal)) ,Territory.Name) | Works | The Aggr around the If(..Sum()) construct ensures that the right dimensionality is given. This works for the total rows and the detail rows, when the dimensionality is Territory.Name. |
5 | Aggr( If(Territory.Name='Germany', Sum(LineTotal)) ,OtherDimension, Territory.Name) | Works | If the dimensionalty is something different than Territory.Name but yet the subset of a specific Territory.Name should be calculated, you need both dimensions in the Aggr. This means, the expression has one deeper dimensionality than the chart itself. However, a grand total row does not work, as it has no dimensionality. See next test ... |
6 | Sum Aggr( If(Territory.Name='Germany', Sum(LineTotal)) ,Territory.Name) ) | Works | Wrap another Sum around the Aggr then the same formula works in the detail rows (like in test 5) but also in the total rows. |
Complex? Yes. I agree. To build such a formula step by step
>>> watch my attached Flash video
Este documento está basado el post Development Checklist. La intención es que sirva para desarrolladores de habla hispana, pero esta vez, el documento debería acompañar la documentación del proyecto en vez de ser una mera herramienta de verificación manual del desarrollo, según la versión original.
Carlos Balenzuela
When it comes to providing metadata, QlikView allows users to provide table, column and field definitions by using COMMENT in the load script. This is beneficial for analysts, designers, and developers. In the below example, I will be going through a simple exercise.
After we load this, the data model looks like the below. When we hover over the table, we can see some basic information about this table, such as number of rows, fields, and keys. Although, this wouldn’t be enough for someone who doesn’t really deal with data modelling.
We can enhance this data model by adding metadata. I also brought in another table called “Sales Person” so that I can elaborate on how to show metadata for more than one table:
First, we created a mapping table called “Table Map” which contains information about the tables in the data model. This could be an Excel file, or a table/view from a database. At the end of the second table, I used COMMENT to display information about these tables. After re-loading the script, we can see that above descriptions appear in the data model:
We can repeat the same process to display metadata for each field in the data model:
When we hover over the fields in the data model, we can see the descriptions of each field:
We can even tag each field as measure or dimension which helps users greatly to determine which fields to use as dimensions or measures:
As a result of this, dimensions and measures appear on the top of the field list when creating a chart:
Hope this helps everyone and I am attaching a blank qvw that has the above codes in the load script.
Thank you.
Other Published Documents on Qlik Community by Sinan:
This technical document explains the enhanced Direct Discovery capability in QV 11.2 SR5. It provides technical details on how to setup the feature, the limitations and the best practices for using this feature.
This series of community blogs is about the practical use of Direct Discovery, the ability of QlikView and Qlik Sense to directly query database tables using dynamically composed SQL statements ...
There are not many QlikView applications without Set-Analysis in one or the other place. There are many reasons to use them
There is NO set analysis supported on DIRECT TABLES There is no TOTAL clause supported in DIRECT TABLES (e.g. SUM(TOTAL <AnyDim> FactField) |
Be aware of the following: If your aggregation expression contains a set modifyer, anything between the angular brackets will be cut off before the aggregation function gets parsed into the SQL statement fired against the direct data source. This means, you won’t get an error saying “Set modifiers are not supported”, the set modification simply gets ignored but the expression ifself will return results from SQL.
But there are workarounds which allow you to cope with that. Let’s discuss two different scenarios of Set Modifications:
In this case, the set modifier can be substituted with an IF() function. Note that for in-memory data tables the recommended way is to use set modifiers even when the following syntax would also work for in-memory data.
This has been discussed in this thread: Direct Discovery: Workarounds for Set Analysis
In this case your friends are Alternate States, which are also supported for Direct Queries. The idea is the following:
The actions needed for this are:
Link the object to that Alternate State. Now it calculates something different than the user’s main selection.
Note: An object is linked only to one State at a time. It cannot query both states at the same time and calculate the difference (typically something you would like to do in comparative analyses). Read on, there are also workarounds to achieve this.
The last scenario is when the user has selected something and the app should make a comparison to some modified selection and calculate differences in the same chart.
Here the so-called Interchart Functions are your friends! Get both, the user's current selection and the comparative selection into a state, then use interchart functions to get the two different results. If the user selected year 2014, additionally select 2013 as well. Then you reduce the selection as discussed under point 1/3, how to get just one year of the total set of 2013,2014.
For details see here here Direct Discovery and Comparative Analysis (3/3)
Hi,
It is related to the following thread
http://community.qlik.com/docs/DOC-7120
And is helpful for those who are unable to open Qlikview document shared in the above mentioned thread and want to build their own application to understand set analysis concepts in a better way.
Thanks,
Geeta
Note:
I managed to make a comprehensive, generic approach for a COMPARATIVE ANALYSIS WITH DIRECT DISCCOVERY
Since Direct Discovery translates queries into SQL, there is no Set Analysis possible. Therefore, we need to approach it in a different way.
► There is a 5" Flash video attached below, in which I am introducing the sample app.
Those are the "ingredients" of this solution:
► Version "v1-13.qvw" is using AdvetureWorks2012 database on an MS SQL Server.
The Qlikview 4 Big Data Demo Series is a collection of demo materials that illustrate the potential of the Qlikview platform to bring Big Data insight to the business enterprise.
Episode 1 - It Starts with Cloudera. Cloudera distributes the CDH, Cloudera Data Hub which is the defacto most powerful Big Data distribution available today.