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‌

This is something that's been asked a number of times, so I would like to share this small tip for everyone. interesting in reading data from Sharepoint lists into QlikView.

There are a number of ways to extract data from Sharepoint.

1. Using WebServices

2. Using RPC-Calls

3. Using Microsoft Access as a Data Bridge

1. Using WebServices

The most common way is using Webservice-calls. However constructing XML-request isn't all straight-forward and can't be done directly in QlikView.

2. Using RPC-Calls

An easier way is to use RPC-calls using owssvr.dll, like:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUID&View=viewGUID

listGUID and viewGUID must be in the format {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}.

The listGUID can easily be found by navigating to the list in Sharepoint and go to List Settings. The listGUID will then show up in the address-field in your browser.

If viewGUID is omitted, the default view will be retrieved. To find the viewGUID locate the list in Sharepoint and activate the view needed. Select View Soure in IE to see the source code. Search for view={ to locate the viewGUID.

The response will be in XML, so when reading into QlikView make sure you select XML Files and Internet File. Add the correct crafted URL as described above. The data retrieved can be found in the node xml/data/row.

Example URLs to be used in QlikView:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}&View={58678A30-DC12-4C66-8568-28E4D9A3EED1}

For more info about owssvr.dll use Google to find various ways of filtering the information

Note: Only fields defined in a view can be retrieved. You need to construct the view with the fields needed, if not using the default view.

3. Using Microsoft Access as a Data Bridge

You can retrieve data from sharepoint using Microsoft Access.

If you are looking at a sharepoint list, click at

Actions --> Open with Access

Microsoft Access opens and a window "Open in Microsoft Access" appears. Select a folder and choose the option "Link to Data on the Sharepoint Site".

Afterwards you can connect from QlikView to this access database via ODBC and you can easily read all fields of the sharepoint list with a SQL Query.

This disadvantage of this approach is, that you'll always have to store your MS Access Database with your QlikView-Sheet. In a server environment this isn't a big problem, but with local clients loading data, this approach has got its shortcomings.

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.

When you are using many tab sheets within QlikView your application can easily become unclear and very full. Using standard tab sheets it is not possible to build a hierarchy in your menu.

I have built a dropdown menu extension that allows you to build a hierarchy within your menu and manage multiple tab sheets in a structured way. The menu is easy to install and use, you can configure it from within your QlikView application.

You can find documentation, a sample qvw file and the extension itself at the following location:

petra-bloem/dropdownMenu · GitHub

Custom QlikView Access Point

https://pomerolpartners.com/upgrade-your-qlikview-access-point/

Getting value out of Business Intelligence isn’t like it appears on flashy flyers. Just because you have a set of reports doesn’t mean that your company will adopt to your infrastructure in the way your project team anticipated. We have found that a common way to drive your BI program is to upgrade your access point. Here is an example of how we have upgraded a client’s access point and we will briefly discuss the benefits.

Top-down View

The above is an access point built in a top down view. There are neat icons and the key benefit here is that this is a Top Down view of the business. The current QlikView access point is in reality a Bottom Up view of your business, as it is a view of the actual operation reports and dashboards.

If you are a business user, you now get to see the extent of the BI Program, as you can highlight the different business functions. Each button panel in this access point is a link to a QlikView Dashboard which is a landing point for underlying reports and dashboards.

It is all native QlikView

An added benefit for Pomerol’s custom QlikView access point is that it has been built on top of QlikView and QlikView Access Point native functionalities and security protocols. Just adjusted for increased engagement for the client.

Appetite for more

We have found that a great way to get buy-in for additional applications and new functionalities from the users is to grey shade some applications that indicates they are not accessible or coming soon into deployment. In this example, applications ‘Engagement’ and ‘KYC’ have a grey background, indicating they are either in-accessible to this user or coming soon/in development.

What's new?

Our access point now has a text section to communicate updates. In this example, we explain the Business Intelligence (BI) Program and news and developments.

Deployment

Deployment for the above access point can be achieved in under a day

QlikView Forums & Resources‌ QlikView Deployment‌

Recently I have heard this question several times, therefore it might be worth of writting something about it.

Since version 17.2, NPrinting has empowered its features to external programs by its RESTful APIs. User may easily control and get data from the system with the APIs now. It is a very popular scenario that user wants to start the NPrinting Publish task right after the data reload is done in the QlikView application, so that the published report will have the latest data.

There are several possible ways to it. In this example you will find how to trigger and monitor the NPrinting task through QlikView load script, which requires nothing more than a QlikView Desktop and a Qlik REST connector, besides of the NPrinting environment. In the end of this article you will also see how to chain it with a QlikView document reload task in the QMC, so that the NPrinting task will automatically start right after the QlikView reload task is finished.

Some prerequisites for using this example:

1. Qlik NPrinting June 2017 and later
2. Qlik REST Connector 1.3
3. A publish task has been configured correctly with successful report generation in NPrinting (help)
4. A user has been configured correctly in NPrinting with proper security priviliges, and linked to a Windows Domain user (help)
5. In order to allow HTTP POST access from QlikView server to NPrinting server, QlikView server’s hostname has been added to the NPrinting Trusted Origins (help)

And this article might be helpful with more information regarding to using NPrinting APIs.

The attached QlikView application in this example is quite self-explanatory. Here I will just list some key components for your attention.

1. Authentication: First I create a POST request for NTLM authentication (POST /login/ntlm). This is because the later request for triggering the task is a POST request. And Qlik REST connector cannot mix between a GET connection with a POST connection once the connection string is created.
   
2. Cookie: NPrinting uses Cookies for handling the Web security. You can find the Cookie information from the HTTP response of the authentication request (/login/ntlm). However you need to manually parse the useful part from the raw response, and apply it as the Cookie in the consequent requests. The example QlikView script showed this process.
3. WITH CONNECTION: I use WITH CONNECTION syntax from REST connector to reuse a POST or GET connection.
4. Looping: For polling the task execution status, I use a loop using "Do ... Loop". Pay attention that you want to drop both the master table and data table within one iteration, so the next iteration can get refreshed data value. I use "IF... THEN" to break the loop because I want to keep the last data table so that the user can have some information about the execution left in the data model. I also use a counter to control the loop because I don't want to wait forever during the debug.
   

The screenshots below show an example of creating a POST connection for authentication in Qlik REST connector.

Download the attached QlikView document and reload it from your QlikView server machine to test how it works.

At last, you may want to create a reload task in QlikView QMC and chain it to the reload task of your data application, so that the NPrinting publish task will be triggered automatically after the QlikView data reload task is completed successfully. You can find the settings from the screenshot below, or get help from this community article.

Thanks for your reading!

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

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

Security in any context is critically important. It becomes especially important
when applied to the enterprise software solutions that organizations rely
upon to make decisions based on sensitive information. Ensuring that the
right people have access to the right information at the right time and no
one else is a critical part of being able to support decision making. Company
performance, employee payroll, sales data, personally identifable information
and forecast information are just some examples of company information that
are commonly represented within QlikView applications.
Security in QlikView is multi-faceted and powerful but is also easy to
implement and familiar to IT professionals. This paper will discuss the
following topics as applied to QlikView:
• Authentication. Who are you? How did you prove it?
• Authorization. What are you allowed to see? What are you allowed to do?
• Implementation. How do you implement security in QlikView?