ICM Testing – Reviewing Results – Part 4

When test scenarios for an SPM application are defined properly, reviewing the results should be straightforward. However because of the volume of results typically generated, finding the correct result can be a very time consuming process without the proper skills (finding output of rule A in the 1 million results generated can be like looking for a needle in a hay stack, except that during testing, there are several needles that need to be found!).

Every ICM application I have implemented offer an interface to browse through the results. This is usually a GREAT tool to troubleshoot and to resolve issues, but it’s rarely the best and quickest tool to analyze results in bulk. Fortunately, every ICM application I have seen also have a functionality to export the results. Exporting the results will create a file locally which can then be opened by a program such as Excel or Access for further analysis. I will discuss how to use Excel to review the results, and later I will create another quick tutorial on using Access.

Step 1) Export the Results from your ICM System
Every system has its own way of doing this, but generally this should be doable from the interface and not requiring a database administrator to pull the data from the database (although this can also be done if required).

Step 2) Import the Data in Excel
First, open Excel and click on File->Open. Click on the dropdown menu beside “Files of type” and click on “All Files”.
Find the location where you saved the exported file of step 1 and click on “Open”.

If the file is a .CSV file, Excel will automatically place the data in columns. If the file is a .TXT file, more steps are required (see step 3).

Step 3: Opening a Delimited Text File in Excel
In the first Wizard step, choose “Delimited” and click on “Next”. In Step 2, select the character used to delimit each column (Xactly uses semi-colons). Click “Next” twice and the data will be imported in columns.

Step 4: Filtering the Result Data
At this point we have a worksheet with all of our result set displayed. Each of the column can be sorted and the search feature can be used to quickly find a specific order. However there is another trick which allows to quickly display data for a specific rule, result name, person, date, etc.

Click on Date->Filter->AutoFilter

This will make the first row (usually column names) dropdown menus which will contain all the values contained within the column. In the example below, the drop-down for column “Currency” allows to choose “USD” and “YEN”. Choosing USD will filter out all the other currency types.

Filtering can be done in this manner for multiple columns at the same time. For example you could filter a certain position (Julien Dionne), and further filter to display only the USD currency.

Quick Summation
Summing credit and commission amounts is one of the most frequent activity I perform when testing.

Excel has a built in feature which allows to quickly find out the sum of selected cells. By simply highlighting the cells containing a number or a dollar amount, the total value of these cells will be displayed in the bottom-right corner of the window.

Additional Notes

Excel offers more advanced functionalities such as Pivot Tables. They can be useful when reviewing a large volume of results. I will discuss this in more details in the future.

Excel Limitations

Excel cannot have more than 65536 rows. If the result set exceeds this, it will not get imported. It is possible to “workaround” this issue by only importing a subset of data (maybe by restricting the incentive period). MS Access could also be used instead of Excel and would not be subject to this limit. I will discuss using Access to review results in the near future.

Editing Fixed Field Length Compensation Text Files, Reports and Mainframe Files

Today my client asked me if I could take the content of an Excel spreadsheet, and export it to a text file in a very specific format. It needed to follow this format to be imported and interpreted properly by other applications. I said the task would be done quickly, before realizing that the spreadsheet had about 10,000 rows, containing complex sales transaction and compensation information which would need to be edited significantly.

When dealing with fixed field length formatted text files, text reports and mainframe files, it seems common that we need to edit something. For example it could be an ID which changed format from 8 to 9 digits and requiring a leading ‘0’. It could also be fields need to be moved around in the file.

To avoid some headaches and save a lot of time, do yourself a favor and download TextPad. TextPad is a simple (FREE) text editor for Windows which allows you to perform simple but powerful tasks.

TextPad offers th TextPad allows you to do vertical selections.

The image below illustrates what I mean by vertical selection.

By holding down the “Alt” key, and performing a “Click and Drag” just like when selecting text from left to right, you can select text from top to bottom.

As I said, I could select the entire Last Name “column” in this fashion, cut it, and paste it before the First Name “column”. I could also select the first character of the first column and delete them entirely. Finally, if I needed the ID here to be 8 digits intead of 5, I could add manually the missing zeros, copy a few rows of those zeros by using the vertical section, and paste them before the location in which you want them to appear.

Again just to illustrate this these would be the steps to insert characters before a column:

• Insert manually the zeros for a few rows

• Select the zeros using the vertical selection method

• Click on copy or press “ctrl-c”
• Place the cursor where the new “column” will appear (in this case just before ID 0070)
• Click on paste or press “ctrl-v”

As you can see, the 2 following rows following 0000017 and 0000023 get the coped “000” appended to them.

That’s all there is to this trick, but hopefully it can, as it did for me, save you countless hours of editing large compensation files row-by-row.