Who’s on 1st

In QlikView/Sense one of the things that comes up periodically is rank. I have seen a number of posts where people have asked about appending a suffix to a number to indicate where you are positioned.

1st, 2nd, 3rd, 4th and so on. There are a couple of excellent posts on the community site giving a solution for a calendar, but what if you want more than 30? I embarked on creating a simple function that could be easily reused. I was also inspired by Steve Dark’s blog post about using formulas to create functions. If you missed it – you can find it here: Quick Intelligence Post.

This combines a couple of operations:

Match – for catching the special cases

Mod – to limit the number of cases

Pick – to do the selection.

You really only care about the last digit, so Pick is perfect for that. You just need to offset things by 1 in order to handle zero. Since this is a formula and you are going to pass in a value when you use it – $1 will represent the number.

Pick(Right($1,1), ‘th’, ‘st’, ‘nd’, ‘rd’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’)

The problem is the special cases. 1st is good, but 11st makes no sense. The exceptions are anything that ends in 11/12/13. This comes up in any multiple of 100, so the way I addressed that is to strip off anything over two digits using Mod() (Short for Modulus). This returns the remainder of dividing by the value provided, so 101 becomes 1, 2414 becomes 14.

This makes the final expression:

set vNumberSuffix = If(Match(Mod($1,100), 11, 12, 13), ‘th’, Pick(Num(Right($1,1), ‘#’)+1, ‘th’, ‘st’, ‘nd’, ‘rd’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’, ‘th’));

This expression is part of a load script, so I used SET to initialize the string but not evaluate it.

You can also set it up in the Variable editor if you are so inclined.

When you use this in an expression it becomes $(vNumberSuffix(numbervalue)), where numbervalue is your numeric field or formula.

Leading zeros and Excel

Excel has a nasty habit of stripping out leading zeros from your data because when it sees something that looks like it might be a number – it wants to use it that way.

This can be a real problem if you are working with things like account ids, or US Postal codes.

When you are importing into QlikView – here is a simple trick you can use to force it to be interpreted as a string and not a number:

Tack a null string on to the beginning of the field.




'' & numericfield1 as numericfield1,

Nice and simple, or was that quick and dirty?


Loading data from QVDs with different structures

QVD files are a great way of dealing with archive data. The problem you can run into is that over time, the fields that you capture in your archive may change. Typically you wind up expanding the number of fields, which was the case I had to deal with.

I did some searches and found some interesting approaches, but I will add my own variant.

Here are the high level steps:

1. Create a header record containing all of the fields you care about, with some initial value. I also like to take one of the keys, and put the letter x in it so I can filter out the header later on.

2. Concatenate load all of the QVDs. Since all of the fields exist, you do not run into any problems with fields getting ignored.

3. Load resident from your temporary table while excluding the record key = x [Optional]

One of the best ways to get started is to open the QVD that contains all of your fields, or at least most of them using a QVD viewer. I like EasyQlik viewer Use the feature to generate a load statement. Make sure you open the one with the most data fields.

It will look something like this:

FROM [myQVDFileName] (qvd);

Now you want to convert this into a record load and give it a name – this will be our header record

'x' as Field1,
'' as Field2,
'' as Field3,
'' as Field4,
'' as Field5,
'' as Field6,
'' as SnapshotDate
Autogenerate 1;

If you have any additional fields you need, just add them to the load above with some kind of value, or just a null string. This establishes the base structure of your table – so no fields will be ignored.

Now you can loop through your QVD files and concatenate them each in turn without fear of losing any fields.

After your loop, you can pull it into your final table and drop the original.


Load * resident tmpArchives
where Field1 <> 'x';

Drop Table tmpArchives;

This drops out your dummy header record and clears out the temp table.


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:

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

LOAD name,
[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]);

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

Complex nested If statements in Qlik

I was working with a client that was having problems working on a fairly complex nested IF statement. I thought that my method of doing those was a really stupid way, but it always worked for me. I showed him the technique and he was able to get his complex if working in very short order. Well, as the saying goes – a stupid idea that works, is NOT a stupid idea.

The example was asymmetrical categories based on number of days.

What i do is to start off writing each part of the if statement like this

If(c, t, f) as myCategory;

c = condition, t = true, f=false

Next, i start to fill things in

if(DaysVariance < -60, ‘really old’, ‘less than -60’) as myCategory;

Next, drop in the next condition

if(DaysVariance < -60, ‘really old’, if(c,t,f)) as myCategory;

This does two things – first and most important – it gets your parenthesis straight, and second – it gives you place holders so you don’t lose track of your conditions.

if(DaysVariance < -60, ‘really old’, if(DaysVariance < -10, ‘sort of old’, ‘less than 10’)) as myCategory;

I use these little text descriptions as reminders as what i am working with at the moment. It doesn’t matter what you wind up with as your values for your end result – using these little reminders helps you keep things straight. Just keep substituting the if(c,t,f) construct wherever you need to go one level deeper, then fill in the blanks afterwards.

How well did it work?

The client was able to get an 8 condition nested if statement working on the first try in less than 30 minutes – a new record for him.


QlikView strings – an adventure in parsing email

Here is the background – I was working on a dashboard on top of a trouble ticket system and we wanted to look at open tickets/cases and determine the last time somebody contacted the customer.

Most of it is straight forward with the exception of the CRM’s ability to have users drag and drop emails into the system, where the contents of the email are saved as text in the notes for whatever trouble ticket it was dropped on.

The problem is that although you know who dropped it in, you don’t know who sent the email, or who it was sent to.

So, having only the raw text of the email, how do you determine if it was an outbound communication to the customer that it was logged against?

(I’ll warn you in advance – this is a long post.)

So – i figure if it came FROM our domain and went TO the customers DOMAIN – then it was outbound. Simple. Sounded like a winner. So the first order of business is to figure out where the different pieces of the email start and more difficult, where they end. Although FROM: is always at the beginning, how do you find the end of the TO: field? It could either be ended by a CC:, a BCC:, or Subject:

And YES, there were a few more twists – but let’s just stick to the basics for now.

So – how do you parse out who it was to? – assuming a table called email_Hist, and field called WORKINGNOTES I did the following:

Left Join (email_Hist)
Replace(Replace(Replace(Replace(Replace(Replace(Replace(WORKINGNOTES, ‘FROM : ‘, ‘|F|’), ‘TO :’, ‘|T|’), ‘CC :’, ‘|C|’), ‘BCC :’, ‘|B|’), Chr(13), ‘~’), Chr(10), ”) , ‘Subject:’, ‘|S|’)
as delimNotes
Resident email_Hist

What this does is change each of the headings of From / To / etc into a standard format delimited by the pipe character, both front and back. The replace operation above can be a bit hard to read only because it is nested through seven replace operations. I also flatten out anything with carriage returns by replace those with tildes. The reason for the pipe characters is to create “book ends” where I can use the TextBetween and not have to worry about what the next field is. This looks like this:

Left Join (email_Hist)
LOAD EventDetailsID,
Replace(TextBetween(dLimNOTES, ‘|F|’, ‘|’), ‘~’, ”)) as fromName,
Replace(TextBetween(dLimNOTES, ‘|T|’, ‘|’), ‘~’, ”) as ToNames
resident email_Hist;

The beauty of this is that now that I have my own delimiters in place – I don’t care what the next field is anymore because I know it starts with a pipe.

Next hurdle – domain match.

In order to use what we have so far to do the outbound domain match you need to have the domain(s) handy. This is turned out to be more complex than I had anticipated because not all company email addresses match the URL of their website. I decided nothing works as well as brute force so for each contact at an account, I stripped the email domain from the address and turned that into a mask to match against.

I started with a raw list of domains, and then turn them into a single string



LOAD ACCOUNTID, ‘*’ & Concat(EDOMAIN, ‘*,*’) & ‘*’ AS DomainMatchList
Resident AccountDomains Group by ACCOUNTID

DROP Table AccountDomains;

Left Join (email_Hist)
Resident DomainList;

DROP Table DomainList;


The idea behind this is that I only care about what comes after the @ sign. I am actually being generous here with my tech support folks because I am saying that if you emailed anybody at the company – I’ll give you credit as an outbound. The DomainList table results in a single entry per account with the DomainMatchList field ready to use with a Wildmatch string operation, since it creates a string that might look like this ‘*domain*’, ‘*domain2*’, ‘*domain3*’
(Yes – I realize I cheated and did some of the string work directly in the SQL, but it was fast and easy to test)

The email_Hist table has a few more fields such as the contact’s last name, email address, alternate contacts last name and email. So taking all the pieces and combining them..

Left Join (TicketActivity)
if(WildMatch(ToNames, ‘*’&ContactLastName&’*’, ‘*’&ContactEmail&’*’, ‘*’&AltContactLastName&’*’, ‘*’&AltContactEmail&’*’)>0 OR WildMatch(ToNames, DomainMatchList) > 0, ‘Yes’, ‘No’) as ToCustomer,
if(WildMatch(fromName, ‘*@ourdomain.com*’, ‘*ourAlternateDomain*’) > 0, ‘Yes’, ‘No’) as isFromUs,
if(WildMatch(fromName, ‘*’&UserLastName&’*’) > 0, ‘Yes’, ‘No’) as isFromUser
Resident email_Hist;

There is some more processing that goes on and then email_Hist gets dropped.

The logic is very generous for giving credit – i.e. if you sent it to the company and/or either of the ticket/case contacts – it counts.

A few notes before the criticism starts.

1. This processing is limited to Open tickets, which cuts down on the data volumes dramatically.

2. The code shown here is optimized for clarity, not performance.

3. I am using wildmatch() > 0 here really just to detect whether what/who i am looking for is in the mix. TO: addresses often have numerous recipients, and I decided NOT to separate those out and look for exact matches. You could certainly use this as the starting point to make it more restrictive.

Hope you find this useful


Controlling # Months displayed in Year over Year Charts

A common request is to display year over year comparisons, and there are a number of ways of accomplishing that.

What if your users do not want to see any of the future months, or even possibly this month, since it is not yet complete?

This actually turns out to be fairly easy – you just need a few pieces to make it happen.

Here are the components

  1. a Variable to hold the # of months to display
  2. Something on the screen that lets the end user control things – I used a slider in this case.
  3. a calculated dimension in your chart.

Step 1 – create a variable. (Settings / Variable Overview / Add). I called mine vCurrentMonth, and set it in the load script to be whatever the current month is based on today’s date.

let vCurrentMonth = Month(Today());

Step 2 – set up your slider control


I also put a Caption/Title and a border on it. It winds up looking like this:


3. The chart dimension

I load a master calendar where I have values set for every date as to MonthOnly, ReportingMonth, Year, and a variety of other flags.

In my chart – I use that in the first dimension


Things to note – I specify a label, and check “Suppress when Value is Null”

and expanded view of the dimension


The reporting year dimension is exposed to the user as a list box, so they can display the year(s) of interest.

The one issue that people may have will come down to preference – this will fill the chart with the number of months that it needs to display. If you want to show the subsequent months as blank spaces, you will probably want to implement this via set analysis in the expression instead of a calculated dimension.