Archive

Posts Tagged ‘pivot-tables’

PowerShell Script to convert your Testing Anywhere run logs into a Excel pivot table data source

    1. If confronted with a sizable Testing Anywhere test script codebase which has been marginally, but not substantially enhanced/cleaned up in several years while producing a barrage of automation errors daily,
    2. you may find that the run suite errors that Testing Anywhere logs automatically in its rlgx files are your best data source  for monitoring and designing a plan of attack:
      1. Any oft-failing scripts should be put last during the daily run? how about length script needs to run?
      2. Any failing  script parts could be modularized and during the daily run? rlgx-excel-pivot-scripts-avg-duration-percentage
      3. any oft-failing scripts? E.g. here the top 8% of failing scripts have almost 30% of the errors. image
      4. Any oft-failing approaches that might benefit from refactoring? Starting with which  scripts? Main actions, then sub-actions:rlgx-excel-pivot-scripts-error-type-countrlgx-excel-pivot-scripts-error-type-count
      5. etc.
    3. Then this PowerShell script may help which
      1. extracts the non binary <runlog> items out of the binary rlgx files,
      2. and merges them into a single file
      3. which it wraps with an XML declaration and root level node that Excel can work with.
 
add-content -value '' -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 Get-childitem -path C:\td\testinganywhere\files\rlgx\arnold-pc1 |
? {$_.Extension -eq ".rlgx"} |  
% { $file = convertto-string $_.FullName  
$match = [regex]::Match($file,'\s+(.*)\s+',"SingleLine,IgnoreCase").value    
add-content $match -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 } 
add-content '' -path C:\td\testinganywhere\files\rlgx\all-a-rlgx.xml -Encoding UTF8 
  1. Make this PowerShell script a Scheduled Task,
  2. So that you can auto-update said XML which you made the  data source for your Excel monitoring/planning work book.
    1. The post-processing of the default error log messages that makes meaningful pivoting actually possible, is left as an exercise to the reader by Testing Anywhere Smiley .

Learning-materials-related posts

Here is an overview of learning materials (Creation service) related articles, per language, on this blog/CMS, including shortcut links that save you building the advanced-search URLS as described in the upper right corner here.

Blogposts

With learning materials or on Creation of

To date 7/22/2014

lm

lmC

Grand Total

Arabic

7

4

11

English

8

10

18

Farsi

7

4

11

French

11

8

19

German

12

8

20

Hebrew

2

0

2

Hindi

7

5

12

Italian

8

6

14

Japanese

9

5

14

Korean

7

4

11

Latin

3

0

3

Mandarin

8

6

14

Polish

5

4

9

Portuguese

9

4

13

Russian

8

5

13

Spanish

9

9

18

Swahili

5

4

9

Yoruba

4

4

8

Grand Total

129

90

219

How to sort a Pivot table by a calculated field column

(Click on image for original size version, now bigger.)
excel-pivot-sort-calculated-field

Mapping of Language Student Locations using PowerView

  1. What do our language students call home? Based on a pivot-table that counts zip codes, let PowerView tell you, which is can now distributed as a free add-in for Excel 2013 Prof, but you need to enable it (under File / Options / Add-Ins).
  2. image
  3. Comes with a nifty zoom: image
  4. image
  5. Click, then Hover a data bubble for more info; image
  6. Like with Bing Maps Visualization, the weighting is surprising: image

2012/3 enrolment per language in the main LRC constituent departments (LCS and ELTI)

excel-pivot-visualization-databars-conditional-formatting-report-express-student-enrollment

How to display rows as percentage of grand total in Excel Pivot Tables

A few notes on Excel’s Version of OLAP cubes: Here is a note detailing the steps taken to display rows as percentage integers of the grand total.

excel pivot