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‌

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 )

• Returns the position of the field value in the field fieldname (by loadorder). If value cannot be found among the field values of the field fieldname, 0 is returned. Fieldname must be given as a string 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 )

• Returns the field value found in position n of the field field name (by load order). Field name must be given as a string value, e.g. the field name must be enclosed by single quotes. The first field value is returned for n=1. If n is larger than the number of field values,NULL is returned.

 Example:

From this above table Field value , 

FieldValue('Customer',2)

Output:

          C) Field Value Count:                                     

                                                    Syntax:    fieldvaluecount ( fieldname )

• Returns the number of distinct values in a field. Fieldname must be given as a string (e.g. a quoted literal).

Example:

From this above table Field value Count ,

FieldValueCount('Customer')

Output:

2.Above Function:

                                                  Syntax:  above( [ total ] expression [, offset [,n ]] )

• Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within column segment in a table or, in the case of image charts, in the chart's straight table equivalent.
• On the first row of a column segment a null value will be returned,as there is no row above.

          From the Above Data Following will be the Outputs,

                  Sum(Sales):       

              Above(Sales):

3.Below Function:

                                                          Syntax:  below( [ total ] expression [, offset [,n ]] )

• Returns the value of expression evaluated with the chart's dimension values as they appear on the row below the current row within a column segment in a table or, in the case of image charts, in the chart's straight table equivalent.
• On the last row of a column segment a null value will be returned,as there is no row below.

          From the Above Data Following will be the Outputs,

                  Sum(Sales):

              Below(Sales):

4.Before Function:

                                                  Syntax:  before( [ total ] expression [, offset [,n ]] )

• Returns the value of expression evaluated with a pivot table’s dimension values as they appear in the column before the current column within a row segment in the pivot table. This function returns NULL in all chart types except pivot tables.
• In the first column of a row segment a null value will be returned, as there is no column before it.

                  Sum(Sales):

              Before(Sales):

5.After Function:

                                                  Syntax:  after( [ total ] expression [, offset [,n ]] )

• Returns the value of expression evaluated with a pivot table’s dimension values as they appear in the column after the current column within a row segment in the pivot table. This function returns NULL in all chart types except pivot tables.
• In the last column of a row segment a null value will be returned, as there is no column after it.

                  Sum(Sales):           

              Before(Sales):

6.First Function:

                                                  Syntax:  first( [ total ] expression [, offset [,n]] )

• Returns the value of expression evaluated with a pivot table’s dimension values as they appear in the first column of the current row segment in the pivot table. This function returns NULL in all chart types except pivot tables.
• If the table is one-dimensional or if the expression is preceded by the qualifier total, the current row segment is always equal to the entire row.

                  Sum(Sales):              

            First(Sales):

7.Last Function:

                                                  Syntax:  last( [ total ] expression [, offset [,n]] )

• Returns the value of expression evaluated with a pivot table’s dimension values as they appear in the last column of the current row segment in the pivot table. This function returns NULL in all chart types except pivot tables.
• If the table is one-dimensional or if the expression is preceded by the qualifier total, the current row segment is always equal to the entire row.

                  Sum(Sales):                

          Last(Sales):

8.Rowno Function:

                                                      Syntax:  RowNo( [ total ] )

• Returns the number of the current row within the current column segment in a table or, in the case of image charts, within the chart's straight table equivalent. The first row has number 1.
• If the chart is one-dimensional or if the qualifier total appears as argument, the current column segment is always equal to the entire column.      

          Rowno():

9.No Of Rows Function:

                                                                Syntax:  NoOfRows( [ total ] )

• Returns the number of rows within the current column segment in a table or, in the case of image charts, within the chart's straight table equivalent.
• If the chart is one-dimensional or if the qualifier total appears as argument, the current column segment is always equal to the entire column.                 

          NoOfRows():

10.Column No Function:

                                                              Syntax: ColumnNo( [ total ] )

• Returns the number of the current row within the current row segment in a pivot table. The first column has number 1.
• If the table is one-dimensional or if the qualifier total appears as argument, the current row segment is always equal to the entire row.

          Column No():

11.No Of Columns Function:

                                                                        Syntax: NoOfColumns( [ total ] )

•   Returns the number of columns in the current row segment of a pivot table.
•   If the table is one-dimensional or if the qualifier total appears as argument, the current row segment is always equal to the entire row.    

          No of Columns()

12.Dimensionality() Function:

                                                                        Syntax: dimensionality ( )

• Returns the number of dimension columns that have non-aggregation content, i.e. do not contain partial sums or collapse aggregates.A typical use is in attribute expressions, when you want to apply different cell formatting depending on aggregation level of data.
• This function is only available in charts. For all chart types except pivot tables it will return the number of dimensions in all rows except the total, which will be 0.                     

                            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.

• If no row is stated, -1 is assumed.
• you can reference field that was not been previously loaded (created on the with AS alias)(See "Bonus 2" example)
• From the Help description "Fieldname must be given as a string (e.g. a quoted literal)."

          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?

How To /Missing Manual(17 articles)

Note:

• This community entry is part of a multi-chapter blog: Start here: Direct Discovery in Action: How to substitute Set Analysis
• This workaround is to make a calculation based on a more narrow selection than the current selection, for example you like to get Sales for Germany only. The Set Analysis would be: Sum({<Territory.Name={'Germany'}>} LineTotal)

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()".

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:

QlikView and Python Integration

QlikView Automation Series – Unzip &amp; Load Excel Files:

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 ...

How to subsitute Set Analysis

There are not many QlikView applications without Set-Analysis in one or the other place. There are many reasons to use them

• Set Analysis is needed whenever something should be calculated based on a different selection than the current user’s selection. E.g. Previous year comparison, count of all transactions, not just the selected …
  
  
• Set Analysis is possible in any of the Aggregation Functions … Sum(), Count(), Min(), Max(), ... It is placed between angular brackets like {1}

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:

1. A set that narrows the current user’s selections (another selection is added, but none is removed from the user’s selections
   
2. A set that extends the current user’s selection (some or all selections get ignored and other selections may be set)

1/3: How to narrow the user’s selections

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

2/3: How to get a different selection

In this case your friends are Alternate States, which are also supported for Direct Queries. The idea is the following:

• The WHERE-clause of the automatically constructed SQL command is derived from the current selections
• An expression, which – technically spoken – requires a different WHERE-clause than the user’s main selection, can be constructed in an Alternate State

The actions needed for this are:

• COPY STATES (from the Main State to the Alternate State)
• One or more CLEAR FIELD or SELECT FIELD

Link the object to that Alternate State. Now it calculates something different than the user’s main selection.

• It will take you some considerations how to sync the alternate state when then user changes something in the main state (when the above set of actions should be repeated, either when clicking a button or silently with a trigger).

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.

3/3: How to make comparative analyses

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:

• This community entry is part of a multi-chapter blog: Start here: Direct Discovery in Action: How to substitute Set Analysis

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:

• During selecting the two sets, I use two different alternate states. Currently, one field from the set is considered for making comparision (can be extended if needed), e.g. YearMonth.
• Once you are done selecting the two sets, the main selection in your dimension must then include the selections from both sets (What's not in the WHERE, won’t be there ...).
• There is no set analysis, but I solved it with intra-chart-functions and MATCH() clauses in the "QlikView-side" of the chart
• I used a Pivot table and put the comparision-dimension (YearMonth) as a column-dimension (it has to be there); the row-dimenion can be choosen by you.
• The "Total columns" calculate the aggregated comparison in absolute and relative changes (the “Total aggregation” in fact is a RangeSum(First(…)…) combination = Intra-chart-function)
• The detail columns show, how the details for the different sets. There are two versions of the chart, one without the details (the results is in the total-part) and one with details. Be aware: both versions require the comparision-dimension as the column-dimension even if the "without detail" chart seems not to use it.
• The SQL Statement uses a “normal” statement and has “no idea what happen on the QV side” … that will keep it performant (No Aggr() etc.)

► 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.