Getting SharePoint data into QlikView

There are already a number of articles on how to do this, but after reading through them and continuing to struggle with it, i decided it was time to also jump in to these waters and try to bring some clarity.

Here is what you are going to need:

  1. The url to use with the owssvr.dll
  2. If you not using the default view, or more than just the default fields in that view, you will also need the views underlying GUID .

The general form of the URL

[sharepoint site url]/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=[GUID for List]&View=[GUID for view - optional]

The GUID for the list is something that you can scrape out of your browser when you access your sharepoint site. Think of it as the record ID for the Sharepoint display. If you don’t specify the view information, you will automatically get the default view and the default fields. If you need more information, then get the view GUID from your SharePoint admin. In my case there were some additional fields that were needed that are not normally displayed, like some internal record keys that link to a different system. If you are not using the additional view information, just leave off everything after the list, so leave off the &View= segment entirely.

You can test out the URL with any web browser, but my preferred method is to use Microsoft XML Notepad 2007 because it presents the data in a nice XML format. If it returns results, then you can move ahead to using the QlikView Web Source wizard. From the script editor, select “Web Files” and it will ask you for an Internet File – enter the URL here.

When you click Next, it should return some results, but it will try to interpret it as delimited text. Change the file type to XML, and it should render into something usable.
It will provide you 3 tables:
xml
xml/data/row
xml/Schema/ElementType/AttributeType

Click finish. It will write the script to load the three tables.

Now for the fun part – making it usable.
The xml table which loads last is basically just a header record and does not provide anything useful, so you can comment that out or just delete it from the script.

row contains all of your rows of data, and if you want to just run with that, you are fine – except the field names will not match what is displayed in SharePoint.That data is in the AttributeType table that it brought in.

The load for the AttributeType table looks like this – with one piece that I added at the end

AttributeType:
LOAD name,
[rs:name],
[rs:number],
[datatype/dt:type] as [dt:type],
[datatype/dt:maxLength] as [dt:maxLength],
[datatype/dt:lookup] as [dt:lookup],
%Key_xml_98C49F546A18E856 // Key to parent table: xml
FROM [http:[sharepointURL]/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=[ListGUID]&View=[ViewGUID]] (XmlSimple, Table is [xml/Schema/ElementType/AttributeType]);

FieldNameMap:
Mapping LOAD
name,
[rs:name]
Resident AttributeType;

This creates a map for the underlying field name to the display name.

To rename the fields in bulk turns out to be simple

RENAME Fields using FieldNameMap;

The remainder of the script

DROP Table AttributeType;
RENAME Table row to mySharePointData;
DROP Field %Key_xml_98C49F546A18E856;

Renaming all of the fields with a map will rename any field that it has a translation for, but pass through any of the ones it doesn’t recognize. If there are any fields that you don’t want the map to touch such as a field that will create synthetic keys, all you have to do is tweak the code for row and rename the field on the way in to something that is not in the map.

About escaping the GUIDs:

i did not have to unescape the GUIDs that I got from the SharePoint admin. If you are not familiar with that term, it means restoring the dashes and brackets to the URL. The { character gets converted to %7B, } becomes %7D, and – becomes %2D. I have seen some posts that insist you have to change them back, but that was not my experience.

Advertisements

Creating a table out of random facts in QlikView

I have run across this a number of times where you have a number of independent calculations needed to create a table of values. Some of the time you run into this as you are pulling data from multiple sources and trying to combine them in a single place.

I had one recently where it was a sales forecasting application.

The sales targets are fixed values, that are just variables in QlikView. With three different targets and two data sources for the sales data – how do you get this all in one table?

There are two components to the solution.

1. For the dimension of the table you use the ValueList function.

ValueList(‘Product’, ‘Service’, ‘Parts’)

This gives us 3 rows for our straight table

2. Now for the fun part – how do you do expressions?

The answer is to use an if statement and use the RowNo function

So – for the Goals, the calculation just becomes

= If(RowNo()=1, [Product Goal], If(RowNo() = 2, [Service Goal], [Parts Goal]))

Following that model, create your additional columns, and add totals, cross column calculations, etc.

One potential thing to trip over is that for some reason, copy and pasting the entire expressions does not work in QV 10. They seem to copy properly, but then they do not display. 

Once i got it working – it was one of those things that is so obvious you wonder why you never thought of it before….

I hope i saved you a few hours there..

Using QlikView formulas for SQL Statements

When I was building the Visual Analyzer dashboards for resale, I found the need to have lots and lots of configuration options to accommodate the different ways people will need to load their information. Since I had spent a lot of time getting creative in how I used variables for display labels, it occurred to me that I might be able to do something along the same lines for the SQL statements that are often repetitive.

In the system usage dashboard, I created an option in place that allows you to filter out the ADMIN user. This means that the only difference between having the ADMIN user data in or not is just a WHERE clause on the statement.

This makes the QlikView code look something like this


If Include_Admin = 'True' then
TableName:
Load field1, field2, .... ;
SQL SELECT field1, field2... FROM tablename;
Else
TableName:
Load field1, field2, .... ;
SQL SELECT field1, field2... FROM tablename
WHERE USERID 'ADMIN';
End if

So.. how about doing this instead?

Let vSQL = "SQL SELECT field1, field2... FROM tablename";
If Include_Admin = 'True' then
Let vSQL = vSQL & " WHERE USERID 'ADMIN'";
End if
TableName:
Load field1, field2, .... ;
SQL $(vSQL) ;

set vSQL = ;

The set statement at the end is to clear out the variable now that we are done with it. It has worked out well in the places I have used it… I just wish I had thought of it BEFORE I hammered out all that code…

ws

SalesLogix multi-select picklists in QlikView

The multi-select pick list in SalesLogix has long been one of the biggest nuisances when it comes to reporting off of your database. The good news in QlikView / Visual Analyzer is that it is a simple thing to remedy.

As an example, let’s use the USERINFO table in SalesLogix. When you set up a user, you can select multiple departments. (I won’t get into how this breaks down, it just makes a good example for now).

In the QlikView load script:

UserInfo:
LOAD USERID, DEPARTMENT AS SOURCE_DEPARTMENT, USERNAME;
SQL SELECT USERID, DEPARTMENT, USERNAME FROM SYSDBA.USERINFO;

Departments:
LOAD USERID, TRIM(SUBFIELD(SOURCE_DEPARTMENT, ';')) AS DEPARTMENT
RESIDENT UserInfo;

You now have a sub-table off of UserInfo called Departments with a one to many relationship.

Miscellaneous Notes:
TRIM is used to take care of any leading spaces,
Semi colon is the delimiter that SalesLogix uses to separate the selections.

As with any 1:M table – you have to watch out for double counting if you are rolling up by department (or any sub-table for that matter).

Last of all – make sure that none of your picklist values have the semi colon character in them – seems obvious from here…. but we all know how creative people can get here.