Category Archives: Productivity

Using JSON as HyperCube Data Payload

Summary: In this post I suggest a case for formatting measures as JSON to allow for easy consumption in an Engine-API webapp.

In a recent customer webapp project with my partners at Websy, I found that using JSON for data in Qlik hypercubes was a useful technique.

The app displays an overview of metrics collected throughout the organization, along with contextual clues and drill & focus capabilities.

The data consists of a varying number of metrics or measures  Measures may be added or removed by appearing in the data. Contextual data such as labels and color coding is provided in the data.

I can’t share the actual customer app.   Here is a simplified data example  to demonstrate with.

 

The real data contains additional complexities, but this sample will suffice.  Using this data, a section of the dashboard output would  appear like this:

 

A row is generated for each Category and columns are generated for each measure.

Current month values, (“Flag_Current=1”),  are displayed alongside available history (“Flag_Current=0”)  for the Date selected in the “Compare to” dropdown.

The CompareDirection is used to to control whether an increase in this measure should be scored as positive  or negative.

There is some asymmetry in the data.  Attributes such as “Format”, are available only in the Current data but are required in the History data as well.  History is available for some measures, but not others.  So what’s the best way to handle the asymmetries?  Not uncommon in Qlik, we can use set analysis and expression functions such as “Aggr()” or “TOTAL”  to propagate the attributes.

As the application requirements and data model grew, the expressions  and sets became more complex and difficult to manage and validate. This was true whether we provided the data as additional columns or qAttributes.

On the javascript side the row and column data are  processed into an object structure to prepare for visualization.  The type of propagation required here — assigning current.Format to history.Format — is trivial in javascript.

Since we are going to transform this data into objects, I thought why not deliver the data as objects already?

The text representation of a javascript object is JSON. Here’s what the Qlik table will look like with JSON output.

 

I created a Qlik script variable  to help generate the JSON.

Set AsJson = '"$1": ' & if(IsNum($1) and text($1) = num($1), $1, '"' &  $1 & '"');

The measure for the “Current Values” column generates an array of objects. The Measure expression is:

'[' & 
concat({1<Flag_Current={1}>} 
'{'
& $(AsJson(Date))
& ', ' & $(AsJson(Label))
& ', ' & $(AsJson(Value))
& ', ' & $(AsJson(Color))
& ', ' & $(AsJson(Format))
& ', ' & $(AsJson(SortOrder))
& ', ' & $(AsJson(CompareDirection))
& '}' , ', ', SortOrder)
& ']'

The definition for the “History Values” is  similar with only the set expression and the fieldnames changing.  The set expression to get this “collection” of data is specified only once and we don’t have to be concerned with repeating the set in multiple definitions.

Javascript to consume the data might look like this.  Note the JSON.parse() to consume the JSON.

const columns = [];
layout.qHyperCube.qDimensionInfo.forEach(info => 
  columns.push(info.qFallbackTitle));
layout.qHyperCube.qMeasureInfo.forEach(info => 
  columns.push(info.qFallbackTitle));
const senseData = layout.qHyperCube.qDataPages[0].qMatrix;

senseData.forEach(row => {
  let currentValues =   
    JSON.parse(row[columns.indexOf('Current Values')].qText);
  let historyValues = 
    JSON.parse(row[columns.indexOf('History Values')].qText);
  let category = row[columns.indexOf('Category')].qText;
  renderRow(category, currentValues, historyValues);
});

I find several advantages in the JSON approach.

  • Fewer expressions and sets to maintain.
  • A closer match between the HyperCube and the object model used in the app, making for easier understanding and validation.
  • Less transformation code on the javascript side.

I’m not suggesting that JSON is always the best way to deliver data.  We found it useful in this particular case and I wanted to share the experience so you could keep it in your tool bag as well.

-Rob

Want to learn more on this topic or advanced Qlik development?   Join us at the Masters Summit for Qlik in Amsterdam (28-30 Oct) or Washington DC (6-8 Nov). For traditional Qlik Devs, we’ll be teaching  advanced skills in data modeling and expressions in the QS/QV Track.  Experienced JS developers will want to attend the Qlik API Track for a deep dive into creating webapps and mashups.

Share

A Common SSE Plugin Project

Summary: I introduce qcb-qlik-sse, a community Server Side Extension to share custom Qlik functions. 

At the Masters Summit for Qlik, I dive into several different methods of creating reusable script and custom functions.  In QlikView we have the ability to write custom functions using VbScript/Jscript in the qvw Module.

Custom functions have been useful for things like regular expressions, geo calculations, url encoding, encryption and others.  I’ll call them “edge functions” — some of us need them, some of us don’t.

Qlik Sense does not have the module facility. How can we satisfy the requirement for custom function in Qlik Sense?  The Server Side Extension (SSE) facility can fill the need and is available to both Qlik Sense and QlikView.

An SSE Plugin runs as a separate task and provides communication with a Qlik Script or chart Expression via a TCP port. The same SSE Plugin can serve both QS and QV.

Anyone can write an SSE. The Qlik team provides the SSE base and you write a plugin that wires your new functions to Qlik. The new functions can be used in both Script and Charts.  A number of plugins have already been produced.

SSE seems to be the ideal place to provide a collection of edge functions.  Rather than a bunch of one-offs, I’m thinking a good idea would be to pool resources into a single effort that could be shared, much like QlikView Components did for Script.

I’ve implemented this idea as qcb-qlik-sse.  This server uses as it’s base the qlik-sse package created by Miralem Drek.

qcb-qlik-sse is written in javascript and runs in node.js.  At startup, the server scans it’s “/functions” directory and discovers what functions are available.  The general idea is that you can add new function by creating a new js file.  You can remove function you don’t want available by deleting the corresponding js file.

See what functions I’ve already implemented in the doc here. I’ve also provided an example qvf and qvw that exercise the functions.

If you want to try it out,  download the project and define the plugin to QS or QV as documented here.  You will also need node.js 8 or later installed.

Defined functions will show up in the suggestion list in both the script and expression editors.

 

If you want to add functions, some javascript skills are required.  Follow the directions in the readme and submit a PR.

I’ve labeled the project as “experimental” at this stage because I anticipate there could be some significant restructuring as I get feedback.

Let me know your ideas and if you find this useful!

-Rob

Want to kick the tires on reusable code and make your Qlik team more efficient? Come to the Masters Summit for Qlik, a three day advanced training event for Qlik Developers. 

 

 

Share

Form Input and Commenting in Qlik

A common desire from Qlik customers over the years has been the ability to interactively edit data on a QV sheet and persist the changes in Qlik or maybe write back to another system. As Martin Mahler of VizLib describes it “turning a one-way data street into a two-way street”.

Sometimes the term “writeback” is used to describe the idea of inputting data in a Qlik app and persisting that data — writing back  the data — to some other system.  The new data is saved and shared with other users of the app. Importantly,  in a form/writeback application, the added data is associated with some row of the data model. For example, in a warranty claims app we may have a dropdown that allows the user to categorize each claim.  The assigned category is used in further analysis.

I think of “commenting” as making annotations on a chart, or chart data point, and a given set of selections.  Ideally, comments may turn into a discussion and use some sort of notification mechanism.

These are not pure terms. There are overlaps to be sure.

I’ve seen some interesting bespoke implementations by partners.  There is also a growing list of off the shelf products that enable writeback and or commentary within Qlik Sense or QlikView.

Some products are Qlik Sense only, some work with both QlikView and Qlik Sense.

Most products allow you to create table sheet objects that mix Qlik DImensions and Expressions with additional input fields such as freeform text, dropdowns or checkboxes.  They all persist the data to some type of backend store such as a database.

When evaluating a product for your requirements, here are some items to consider:

  • Do you have requirements for read-only and update users?
  • Are you looking to add additional data in a single chart or do you need to reference the new data from multiple charts?
  • Are you looking to add one to one new data or build complex workflow apps?
  • Does your business objective require structured form data,  free-form commentary or both?

There are an interesting range of products and capabilities out there.  Klikins and Emark Forms for example let you add new fields to a straight table.  One of the more interesting approaches is K4 Analytics, which embeds Excel into Qlik. This provides the full range of Excel formula and formatting functions. You can build some pretty powerful aps this way.

There are products that focus on finance reports. TrueChart creates a set of functional and beautiful IBCS compliant reports along with a nice navigation interface that can be reused throughout the Qlik app.

Both TrueChart and Climber Finance Report support the type of commenting and annotations user require in finance reporting.  I’m excited that the Climber commenting is being expanded and released as a generic commenting & collaboration product by VizLib. Qommentary is another global commenting solution.

Here in no particular order are some Writeback / Commenting products I’m aware of. The headings are links to the vendor site.

I’m sure I’ve missed some products.  Feel free to leave me a comment if you have something to share.  It would also be good to hear about use cases where you have found value in implementing an input/writeback solution.

Inform Write 

QlikView and Qlik Sense

K4 Analytics

QlikView and Qlik Sense

TrueChart

Qlik Sense

Emark Forms

Qlik Sense

Klikins

QlikView and Qlik Sense

Pomerol Writeback

Qlik Sense

Qommentary

Qlik Sense

VizLib 

Qlik Sense

 

Share

Catwalk — The Alternative Data Model Viewer

Summary: I introduce “catwalk”, an alternative data model viewer for Qlik Sense from the Qlik oss team.  Don’t like reading?  Go to https://catwalk.core.qlik.com/ and give it a spin. 

I’ve been having a lot of fun with catwalk, a fairly new tool from the Qlik oss team.   I call catwalk an “alternative data model viewer” for Qlik Sense.

I’m going to start by showing a few screenshots and then tell you how to get started using catwalk.  It’s easy to try out.

After selecting an app to view, you’ll get a graphical table & field layout.  In addition to a visual depiction of linkage, you’ll get some rich information about cardinatility, relationships  and some nice explanations of subset ratios.

You can also make field selections and see how those selections impact the other tables.  A nice little tool in the lower right corner lets you build hypercubes (straight tables) on the fly to visualize aggregations.

I’m not going to tour all the features here because the first time you enter catwalk you’ll be offered a walkthrough guide.  I highly recommend you take this brief guide.  You can return to the guide at any time from the … menu in the upper right.  A tip on the guide: When it says “you can do X, try X” it won’t let you continue until you try actually try X.  Clever.

 

So how do you get access to all this goodness?  Go to the github page https://github.com/qlik-oss/catwalk for instructions.  Don’t like reading instructions?  Make sure your QS Desktop is started and go to the hosted version at  https://catwalk.core.qlik.com/.  Have fun.

The connection to your Qlik Sense server is from your local browser. No data is passed to the server hosting catwalk.

A really cool way to invoke catwalk is to set up a bookmark with the javascript shown here https://github.com/qlik-oss/catwalk/tree/master/bookmark.  Click the bookmark while in any app in the hub and you’ll open catwalk on that app.   Simple.

So while catwalk may have been conceived  as a data model explorer for Qlik Core (which has no built-in viewer) it’s just as valuable for Qlik Sense Enterprise or Desktop.

Have fun!

Share

Understanding and Using Subset Ratio

Summary: If you are familiar with subset ratios in Qlik, you may not find much new in this post. But if you are new to Qlik or are unfamiliar with subset ratios in your data model, please read on. 

When loading data into Qlik and building a new data model, inspecting the Subset Ratio of key fields is an important exercise to ensure data quality.

Subset Ratio is displayed in the Preview Pane (Qlik Sense Data model viewer) or Table Viewer (QlikView) when a key field (field linking two or more tables) is selected.

After clicking a key field, in the Preview pane you will see three important numbers:

Total distinct values:  The count of all distinct values for this field (CustomerId) from all tables (Orders” and “Customers”) in the model.

Present distinct values:  The count of distinct values for this field (CustomerId) in the currently selected table (Customers).

Subset ratio: “Present distinct values” divided by  “Total Distinct Values”.  What percentage of total field values are represented in this table?  In this case, 100% of all CustomerId values in the data model are represented in the Customers table.  This is good. We will typically expect to see 100% Subset ratio in a dimension table.

Let’s take a look at the Subset ratio for the same field in our fact table — the Orders table,

It’s less than 100%.  Our Customer table is our “customer master” and represents all of our potential customers.  Our Orders table represents a limited time period, perhaps 12 months.  Only 44 distinct customers, or 22%,  are represented in the set of Orders we have loaded.

Less than %100 Subset ratio is a normal condition for a Fact table.  If we don’t want to include Customer data for those customers who have no orders, we can filter the Customer load with a “Where Exists(CustomerId)” clause.

So far we’ve seen “normal” subset ratios.  Let’s look at some exceptions.  What does it mean when the dimension table (Customers) has less than 100% subset ratio?

It means we have an order(s) that has no link to a Customer row.  That’s a data quality problem. In the example above we can see that we have 1 missing CustomerId (201 – 200).

Why do we have a missing Customer?  We would have to dig into the data to find out why.  It could be that we have loaded historical orders and “inactive” customers are archived from the Customers table.  It could be that we have some bad data due to a bug.  We have to analyze the data and decide on the best path to remediate.

By the way, what is the specific value(s) of the missing CustomerId?  A simple way to make this determination is to create a table chart with two columns — The key field and a field that has 100% density (every row has a value) from the Customers table.  I’ll use the CustomerName field. Sort the table by CustomerName and the key value in question will show at the bottom of the table with a null value for CustomerName.

What does it mean when the sum of the subset ratios for two tables equals 100%? It means there are no matching values between the two tables.   This can happen for instance when the keys come from two different systems that use slightly different nomenclature.  Perhaps in your ERP all ProductId values start with “P” but in the spreadsheet that someone provided for additional part info the “P” is excluded because none of the humans use the “P” when identifying parts.

Examining Subset ratio as you build up your data model is an important quality step.  Validating the quality of your data model will make the process of creating visualizations go much smoother.

-Rob

 

Share

Running QS Regression Test in Batch

If you’ve ever seen my presentations on automated testing of Qlik apps, you know I’m a big fan of the free Qlik Scalability Tools  — both QV and QS.

I use the Regression Test feature to automatically validate Qlik app changes before promoting to production.   Regression testing allows us to validate that charts continue to produce correct results after updates are made to the application or platform software.

This graphic provides a brief overview of the regression testing concept.

The Qlik Sense Scalability Tool (QSST) provides a GUI workbench to run and analyze regression tests.  The workbench is a great tool for a developer or QA Analyst to validate an app on an adhoc basis.

What if I want to run the analysis unattended on a schedule?  For example, after every reload to validate that my app is still working correctly and my expected data was loaded.

In this post I’ll demonstrate using a system monitor to run the regression test as an ongoing validation and send an alert if the validation fails.

I won’t go into the details of setting up the Scalability Tool.  You can get that from the product documentation.  If you would like me to do a presentation on automated testing for your team contact me here.

We will need a system monitor that is capable of running commands on a schedule, monitoring the command output and sending alerts or status via something like email or Slack.  You may already have a capable system monitor installed at your site.

NodeGraph is my current favorite Qlik-centric testing tool for ongoing quality monitoring (I’m a partner).  NodeGraph’s Test module allows for testing expression values,  presence of fields and update times.  At the moment NodeGraph does not support running QSST Regression tests, but I expect it will be part of the product by 2018Q4.

For the time being, I’ll demonstrate batch regression testing using Woodstone’s Servers Alive, a low cost but powerful system monitor I’ve used in the past.  You can try Servers Alive for free.

Let’s assume we have used the QSST workbench to author the regression test scenario and have executed the scenario to create a  baseline log.  When using the workbench, we would re-execute the scenario at a later time to create a compare log and then analyze the differences between the logs using the workbench Analysis tool.

QSST provides command line versions of both the scenario executor (“SDKExerciserConsole.exe”) and the regression log analysis (“RegressionAnalyzerConsole.exe”) .  We will need to bit of script to stitch these two operations together and process the output.

I’ve posted a powershell script here that wraps all the necessary operations and exits with an ERRORLEVEL of “1” if validation fails (differences found between compare and baseline logs), or “0” if no differences are found.

  1. Download the RunRegressionTest.ps1 script and place it in a directory named “Regression”.
  2. Create a subdirectory under Regression named “baseline”.
  3. Copy your baseline log file for each app of interest to the baseline directory.   Do not rename the file.
  4. Following the comments in the file, update the first three variables in  RunRegressionTest.ps1 to identify the location of your QSST install and your scenario json file(s).

We can check that the script runs correctly from a powershell command prompt in the “Regression” directory.

.\RunRegressionTest.ps1 "ABC Sales Demo"

where “ABC Sales Demo” is the name of a json file in our scenario directory.  If all goes well, we should see output messages like:

PS C:\QlikSense-Projects\Regression> .\RunRegressionTest.ps1 "ABC Sales Demo"
 Comparing C:\QlikSense-Projects\Regression\temp\results\ABC Sales Demo_localhost_[1-0-1--1]_18072311263304_regression.log
 to baseline: C:\QlikSense-Projects\Regression\baseline\ABC Sales Demo_localhost_[1-0-1--1]_18072216105188_regression.log
 Differences found

Now to implement this in Servers Alive.  In Servers Alive we define things to test as “checks”.  In this case I’ll use the External(errorlevel) check type and provide the command string to run my powershell script.

The full command string is: (no line wrap)

powershell -NoProfile -ExecutionPolicy bypass -File "C:\QlikSense-Projects\Regression\RunRegressionTest.ps1"  "ABC Sales Demo"  "p2"

where “ABC Sales Demo” is the name of my scenario json file.  “p2” is a dummy parameter that works around a windows bug.  Without that extra parm, a blank space is added to the previous parm causing a failure.

On  the Alert tab I’ll specify to send a message to Slack on failure. On the schedule tab I’ll specify when to run this check.

All set up!  When run either manually or on schedule, a failed check — that is, the regression test found unexpected differences — the check will display red on the SA console and I’ll  also receive a Slack message.

 

 

 

 

I’ve just introduced a number of moving parts.  If this is all new to you what I hope you’ll take away is that formal, structured and automated monitoring of your Qlik Applications is possible with relatively low cost and effort.

Want some assistance with planning your testing strategy and implementation?  I offer consulting in planning and implementation of automated testing and monitoring for your Qlik environment. Contact me if you want to chat about your needs and how I can help.

I also typically do an optional lunchtime session on testing at the Masters Summit for Qlik.  Come along to Philadelphia or Johannesburg to talk testing and many many more Qlik topics.

-Rob

 

 

 

Share

Expanding the Qlik Sense Editor Pane

Summary: In this post I present a non-intrusive bookmarklet to hide the Sections pane in the Qlik Sense Script Editor to provide more real estate for typing script. 

EditNote in the comments below that hiding the pane can be done out of the box using keyboard shortcuts such as Alt-F11.   That reminds me to read the doc! Nevertheless the concept of bookmarklets is useful and this post is bringing in some interesting contributions. 

I sometimes wish for a larger window in the Qlik Sense Script Editor where we type  statements.  This is especially true when I am projecting and I’ve zoomed my browser to make the text legible to the audience, or I’m saddled with a very low resolution.

The UI allows me to hide the Data Connections pane which provides  more space, but I’m still left with the Sections pane consuming 250 pixels on the left which may not serve me at the moment.

Wouldn’t it be great if I could hide/show the Sections pane on demand, like I can do with  Data Connections? Here’s a simple non-intrusive hack that will allow you to do just that.

Paste the following code in a bookmarklet. If you’re not familiar with bookmarklets, they are small bits of javascript code that can be executed from the bookmarks menu.  If you are using Chrome as I do, it’s as simple as pasting the javascript code in the URL property of a bookmark.  Google to learn more about bookmarklets.  If you are using a browser other than Chrome google to see how to create (if possible) bookmarklets in your browser.   Here’s the javascript:

javascript: (function () {
 var size = ($('.scripteditor-left').css("width") == "0px") ? "250px": "0px";
 $('.scripteditor-left').css("width", size);
 $('.scripteditor-stage').css("left", size);
 }());

Apply the bookmark and here is what the editor looks like now:

Apply the bookmark again and the Sections pane reappears.

 

This is an unsupported hack of Qlik Sense.  If something else does not work in the editor, you should refresh the browser — which will completely remove any effects of the bookmark — before suspecting or reporting that Qlik Sense has a defect.

This is  a very clean technique to add behavior because we have not modified any Qlik Sense files.

I file my QS bookmarklets in a Bookmarks Bar folder named “QS” which gives me easy access in a dropdown to mods I’ve created for Sense.

Here’s another bookmarklet I find useful.  This one opens the Qlik Sense script log folder for QS Desktop.  My userid in the path is hardcoded, you would of course update to the correct folder name for your machine.

file:///C:/Users/rob/Documents/Qlik/Sense/Log

Please share if you develop additional useful bookmarklets for Qlik Sense.

-Rob

 

Share

Exploring Data Lineage with NodeGraph

In the Qlik world, we are frequently faced with questions like:

  • Where did this field come from?
  • What applications (if any) use this QVD?
  • If I change this database table, what applications will be affected?
  • Am I creating QVDs that are not being consumed?

I’ve maintained a QlikView lineage application for my customers over the years.  I was never completely happy with it as Qlik lineage metadata is inconsistent and the critically important field level lineage was never available.

All that changed when I discovered NodeGraph.  Now I’m a NodeGraph fan and partner.

NodeGraph is an add-on for your QlikView and Qlik Sense solutions that allows you to explore, visualize and trace where your data comes from, whether it’s up-to-date, how it’s calculated, and much more.

NodeGraph scans both QlikView and Qlik Sense files and produces a graph of applications and data in a beautiful easy to navigate interface.  Select any node, for example a dashboard QVW, and lines trace the data lineage through transformed QVDs all the way back to the source database.

 

You can search, filter and drill in any direction;  from applications,  QVDs, source tables, fields, charts or SQL queries.

Where did this field come from?  Right-Click any field and select the Field Explorer to get a report of the complete lineage for this field, including all script transformations!

 

I’m looking at a chart that shows a count of Customers.  How was that count calculated? Where did the data come from?  In the Content view I can review the expression and see the lineage for fields used.

 

One of my favorite features is the one-click application documentation.  The tool generates a pretty printable/PDF document suitable for Auditors that describes all data sources, transformations, and expressions used in the selected application.

 

There are more uses and features in NodeGraph than I can cover in a blog post, including governance reports, application usage, GDPR compliance and testing.

If you are coming to Qonnections,  see NodeGraph in action at the TechLab10 booth (#603) in the Discovery Expo.

Learn more about NodeGraph at nodegraph.se. If you have questions or would like to schedule a free demo,  reach out to the NodeGraph team on the website or info@nodegraph.se.  If you are in North America you can contact me directly for questions or to scheduling a demo.

-Rob

Share

QViewer Acquisition

I’m pleased to announce that I’ve acquired the excellent QViewer tool from Dmitry Gudkov effective January 1.  I’ll be marketing, developing and supporting QViewer going forward.

QViewer has a stellar reputation as a must have tool for Qlik Developers.  I’m pleased to be taking over such a fine product.

This acquisition is one step in my plan to focus more on software development during 2018.  Stay tuned for other announcements during the year.

Existing licensed QViewer customers should email future support requests to support@panalyticsinc.com.

Happy New Year!

-Rob

Share

Mass Editing of QVW Script

Summary: In this post I describe a process using freely available tools to apply changes to all scripts in a set of QVW files.

So you have a lot of QVWs. And now you are asked to identify and make updates to all scripts to support changes such as:

  • A change in QVD or other file naming.
  • Changes in file paths due to a server move or directory restructuring.
  • Updating file paths to use variables.

If you are super cool, all those items are represented by variables, changes are handled by updating a single include file and you can relax and stop  reading this post. For the rest of us, read on.

You can scan and search all your script using my Script Repository tool.  That will allow to identify where changes are required, but do you have time to edit every QVW and make the changes?  Easy enough for a few, but what about when you have dozens?

QlikView Desktop has a useful facility we can leverage for mass changes; the “-prj” folder.  If a folder named qvwname-prj  (case sensitive) exists in the same folder as the QVW, when the QVW is saved, QV Desktop will write a set of text files representing the structure of the QVW to the -prj folder.  One of those files is “LoadScript.txt” which contains the load script.

When QV Desktop opens a file, it checks for the existence of a companion -prj.  If found, it populates the QVW with the contents of the files in the -prj.  If we change one of those files, for example “LoadScript.txt”,  that change will be inherited by the QVW.

Let’s walk thorough a scenario where we can utilize this feature to update the scripts of an entire set of QVWs.  I’ll utilize free tools that will make the process easier.

My sample problem is this: I have inconsistent QVD naming conventions. We’ve decided that “DimCustomer.qvd” shall henceforth be known simply as “Customer.qvd”. I’ll need to update the script that generates the QVD as well as all readers of the QVD.

I will accomplish this update in four steps:

  1. Create -prj folders for all QVWs.
  2. In the “LoadScript.txt” files replace “DimCustomer.qvd” with “Customer.qvd”.
  3. Rebuild the QVWs with the updated -prj.
  4. (Optional) Delete the -prj files.

The sample I’ll use for this post is relatively small to keep the demo simple.  But I’ve used this technique to process hundreds of QVWs at a time incorporating several different script edits.

I have a directory of QVWs that looks like this:

 

 

In the SubFolder “Loaders”, there are additional QVWs.

 

 

I’ll need a -prj folder for each QVW. I  can create the -prj manually, but this is where I can leverage the PrjTool to make life easier.  You can download the PrjTool from the Tools section of this site.  (Note: If you received a copy of PrjTool from the Masters Summit, please download this newer version as it contains important updates.)

PrjTool requires a Directory as input and the selection of one of  three functions:

  • BuildPrj: For all QVW files found in the specified Directory, create a -prj folder.  This includes opening and saving the QVW to populate the -prj.
  • CreateFromPrj: For all -prj folders found in the specified Directory, open and save the QVW to update the QVW with contents of the -prj.  If no QVW exists, a new one will be created.
  • DeletePrj: Delete all -prj folders found in the  specified Directory.

I’ll start by specifying the Directory that holds our QVWs and selecting the BuildPrj function.  Press the Execute button and the script will launch. The execution may take some time as each QVW has to be opened and saved. Good time to go for a coffee.

When the execution completes the log window will be filled with messages listing the -prj folders created by the tool.

 

If we examine the directory again we will see the new -prj folders.

 

Our next task is to edit the LoadScript.txt files. We can use any editor capable of searching and replacing across multiple files.  For this demo I will use the free NotePad++ editor.   From the NotePad++ menu, launch “Search” , “Find in File”.  In the search dialog I specify the Directory  and  the search and replace strings. I’ll also limit the search to the LoadScript.txt files.

 

After pressing “Find All”,  I’ll get a list of search results.  I can double click any of the results to open the file for further examination.

 

When I’m satisfied that I’m going to make the correct updates, I again launch “Find in Files” and press “Replace in Files”  to perform the update.

Now I’ll use the PrjTool again to update the QVWs with the updated -prj files.  I run the tool again, this time selecting the “CreateFromPrj” function.  Again, if you have a lot of large QVWs, be patient while the tool runs.  The resulting log messages will inform me of the updates.

We’re done!  All QVWs now contain the updated load script.  Optionally we can run the tool again with the “DeletePrj” function to delete the generated -prj folders.

You should always perform this kind of mass update activity on copies of QVWs and audit the results.  Also, never use -prj folders in production.  Server reloads do not recognize -prj folders.

-Rob

 

Share