All posts by Rob Wunderlich

QSDA Pro as a Quality Tool

I my last two posts I introduced QSDA Pro and the flag workflow.  In this post I’m going to show how you can use QSDA Pro to quickly uncover quality issues in your Qlik Sense App.

QSDA groups flags by category, and the “Quality” category is where I usually begin my app review.  Quality flags are used to indicate where something appears to be seriously broken such as:

  • An expression syntax error.
  • A reference to a Master Measure that no longer exists in the library.
  • A missing extension.
  • A bookmarked field that is no longer exists in the data model.

These are the kinds of things you want to discover before your users do!

It’s relatively easy to create new errors in an existing Qlik app.  Qlik does not warn or block you from deleting a resource like a Field or Measure that is in use.

A properly done field rename may update names in expressions.  But it will not update variables or bookmarks.  And I’ve seen plenty of field renames go wrong and break expressions as well.

It can be difficult to detect all app errors by visually checking your app.  The broken piece may be a color expression or a calculation condition that isn’t readily visible.

So yes, easy to break, sometimes hard to detect and heros find  problems before their users do.  Enter QSDA Pro.

In QSDA menu,  Help -> Flag List will display a list of all potential flags in the installed version. Here’s the current list for Quality (The “{}” bits are values that will get filled in when the flag is created.)

Some of these are fairly straightforward, some a bit more subtle.

Why would you have a missing Master Dimension or Measure?  A fairly common scenario is not understanding that Master Items are referenced by internal Id, not by Name.  So you create something called “Sales” and use it in several charts.  Later you are doing some exploration and create something called “Sales2” which you like better. So you think if you delete “Sales” and rename “Sales2” to “Sales” everything will be using the new “Sales”.  Wrong.

Because the master item is now gone, I can’t tell you what used to be in this master item.  You will have to rely on an app backup. Or…if you have a previous QSDA Analysis for this app you can look up the master item definition there!

All Expressions and Dimensions are validated using the Qlik syntax checker. A typical error might be a bad fieldname (perhaps because the field was removed from the model).

Flag Details will give us a detailed error message, location of this expression  (Master Library) and importantly the Use Count. This use count is zero so we know deleting the measure is a viable option.

QSDA uses the Qlik syntax checker so the test is only as good as what I  get from Qlik.  There are some limitations, for example when column labels are used in an expression.  You’ll note that the Qlik expression editor flags this as invalid so QSDA will pass this message on.

The “Parse Error” flag is raised when Qlik Syntax says the expression is ok but QSDA finds the expression does not conform to expected syntax rules. This could be a problem with the QSDA parser  (a work in progress) or it could be Qlik tolerating something unusual.  For example, do you think this is a valid expression?

sum({<Sales={">10 between < 90"}>}Sales)

The Qlik syntax checker will declare it valid. And it will return an official looking result — which may or may not be correct.  But what is that “between” keyword? (if you’re curious why “>10 के बीच < 90” also seems to work, see https://masterssummit.com/something-to-get-confused-about/).

Nervous about refactoring or changing your apps?  Can you relate to “Change Paralysis”  or Fear Driven Development (FDD)?  We all need a tool like QSDA Pro to proactively plan changes as well as audit after the fact for unintended errors.

Download QSDA Pro now and analyze some of your own apps.  QSDA Pro is free to use during the beta period.

You can analyze published and unpublished apps, so go ahead and take a look at some of those production apps.   Let me know in the comments if you find something interesting!

-Rob

 

 

 

Share

QSDA Pro Flags

In my first post introducing QSDA  Pro, I told how Document Analyzer was a tool I developed for use in my own consulting work and shared with the community of Qlik developers.  The Qlik Sense QVF version got a big boost in utility when Axis Group joined the project and contributed their many many years of Qlik experience.

In this post I will  introduce the  Flag workflow of QSDA.

QSDA communicates it’s advice through “Flags”. Flags are issues that warrant your attention.

The Summary page gives an overview of Flags for this analysis. Clicking any of the Flag boxes or Flags in the Nav Ribbon will take you the Flags page.

The Flags page provides a collapsible list of all flags and buttons for more detail and flag status.

 

Flag text identifies the error and in most cases provides some detail, such as a fieldname or number of bytes that could be saved.

The dimension failed validation. The error is: Bad field name(s) "[PostalCode]" .

Click  to  show the Flag Details panel.

The details panel will provide more context for the flag to help you evaluate your response.  Depending on the flag, there may be additional hyperlinks.  Click the “?” icon to link to the Help article for this flag type.

To support a workflow of “checking off” the flags,  buttons and flag status indicators are available in the both the Flag Details and Flag List views.

 Mark this flag instance status  as “Resolved”.

 Mark this flag instance status  as “Hidden”.

Resolved and Hidden flags are not shown in the list unless the filter is activated on the filter bar. Flags with no status are “Unresolved” and are always shown.

Buttons will automatically toggle to allow you undo a status. You can set/unset statuses on individual flags or at the flag type level.  Hover over a button to discover its function.

From the Flag List you can view all flags.  In the resource pages, you will also see the same flags but attached to the resource.  For example, on the Dimension page,  any associated flag will be shown with the specific Dimension.

These workflow features allow you to punch through the list of issues in your app, hiding those you don’t care about and checking off issues as you resolve them.

Learn more about QSDA Pro and download the latest version.

-Rob

 

 

 

Share

Introducing QSDA Pro

QSDA Pro is the next generation of my Document Analyzer tool for Qlik Sense.  I published the first Document Analyzer for QlikView in 2009 followed by the Qlik Sense version in 2017.  I think it’s fair to say both are the most widely used Qlik developer add-on tool.

Qlik Sense Document Analyzer (QSDA) has been extremely useful. But it’s had some limitations, especially in the server environment. I’ve wanted to address those shortcomings,  and now I bring you my response :

QSDA Pro is a completely rewritten standalone app that provides  features of the current QVF based QSDA and more.  QSDA Pro can analyze and provide advice on apps in QS on Windows, Qlik Cloud, and QS Desktop.

QSDA can help you produce cleaner, smaller and faster apps by identifying:

  • Unused resources, such as fields, that can be removed.
  • Errors in your app such as missing fields, syntax errors or missing key values.
  • Bad practices and recommendations on how to remediate.
  • Highlight performance hotspots.

QSDA Pro can do all of the above and more for public sheets and private sheets.  “Is anyone using this field in a personal chart or bookmark?” is no longer an unknowable thing.

QSDA is also your goto companion for those ad hoc tasks like modifying use of a field. Maybe a renaming or dividing by 1000.

Where am I using the field “Company Name”?  Easy.  Filter on the field name and I can instantly see the field is not referenced by Variables, but it is used in 22 Charts, 23 Dimensions and 5 Expressions. Oh and 1 Bookmark.

What Charts where?  Click Viz on the ribbon to get a list.  I can explore the chart Dimensions and Expressions there or click a link to open the associated sheet in the Qlik Sense Hub.

QSDA is smart! It can determine that all four of these measures calculate the same thing, even with differences of variables, casing and whitespace.  And it recommends you should settle on one Measure while very helpfully showing how many times each variation is used to help you in your decision.

In the coming weeks I’ll be posting more about the capabilities of QSDA Pro.  I’ll also be responding to feedback on the QSDA Pro Community site.

QSDA Pro is currently in public beta testing and is free to use during the testing period, which is expected to last for several months.

You can learn more about QSDA Pro and download your own copy.

Licensing terms for the supported production version will be announced later. There will be a free tier which may have  limits on volume and  features.

-Rob

 

Share

Announcing the Qlik Illuminati Class of 2021

You may have heard by now that the Qlik Luminary program has shifted to be more customer focused, which I think is good.

Those of us who were previously Qlik Luminaries are proud to have received the designation “Luminary Alumni” but some of us don’t  go so quietly.

That is  why we have formed the “Qlik Illuminati”, a shadowy cabal of former Luminaries.

As soon as this pandemic thing is over, we will be holding our first worldwide meeting in a secret  ice cave or mountain bunker somewhere (If you have an ice cave or mountain bunker available for rent, please contact our events coordinator).

Do you doubt our power and influence? Ever wonder why you see Mike Tarallo only on the “green screen”?  That’s because we abducted him and Ralf distilled Mike into pure code.  MikeBot now reads our scripts! We let physical Mike go  (we’re not brutes, we’re technicians) but an unfortunate side-effect of the distillation process is that Mike now seems unable to focus on a single topic for more than 60 seconds.

We have been incredibly successful in getting our “Q” symbol insinuated everywhere — in social media, signs at demonstrations, footage  on the nightly news. Everywhere.

The “R” users think we’re crazy — they think they are the real deal. But we know that Q are the rational ones.

I bet you’re looking for a list of Q Illuminati members. Sorry I can’t share that with you. We are a shadowy organization. (Ralf, did I say Ralf? I meant Ralph! Oh well…)

Yours Truly,
Q

 

Who started this April 1 foolery anyways?

Share

QViewer + Easymorph, Better Together

Thousands of Qlik Developers use my QViewer QVD Viewer tool every day to browse inside QVD files.  Sometimes you want to do more than browse. I get questions like

  • Can I sort the rows?
  • I want to sum field x grouped by field y. Can I do that?
  • Can I get a histogram of field values?

The answer to all of the above — and more — is Yes!  When you install the free Easymorph Data Prep and ETL tool, you can use the powerful features of Easymorph like an extension to QViewer.  With Easymorph you can sort, query, transform and profile your QVD data.

When viewing a QVD, press the button on the menu bar. The current QVD will open seamlessly in Easymorph.  If Easymorph is not yet installed you will be taken to a webpage with download and install instructions.

You can also open Easymorph by right-clicking a QVD from QViewer folder view.

The QVD will open in Easymorph and you can then use all of Easymorph’s powerful features with your QVD.

Easymorph has plenty of help and an active Community to help you get started.  I’ve also prepared a couple of Help articles in QViewer showing some typical QViewer/Easymorph  tasks.

-Rob

 

 

Share

Can I Update a Mapping Table?

Summary: Can you concatenate rows to a Mapping Table? The answer is “Yes” and I show you how.

Recently my Masters Summit colleague Oleg Troyanksy asked me “What are the limits on updating an existing Mapping Table in Qlik script?”.  My immediate answer was “You can’t update a Mapping Table after it’s been created”.  Then Oleg showed me a script that surprised me — adding to a mapping table in a loop,  implicit concatenation.  The loop works, but what if we need to load from multiple sources?

When the contents of a mapping table come from multiple Load statements, I have always advised doing standard loads to a temp table and then a final Mapping Load from the temp table.

Turns out you can concatenate to a mapping table.  Maybe that temp table is unnecessary.  I don’t find any doc on the topic. Here’s what I’ve found from experimentation.

We may wish to do something like this:

MapX:
 Mapping Load * Inline [
 from, to
 1, A
 2, B
 ];
 
Concatenate (MapX)
Mapping Load * Inline [
 from, to
 3, C
 ];

That script will fail with  “Table ‘Mapx’ not found” error. You cannot directly reference a mapping table this way.

Interestingly, if we leave off the “Concatenate (MapX)”, it will concatenate and result in the desired mapping.  Implicit concatenation will kick-in and the second load will add rows to the mapping table.  I’ve included some ApplyMap() code in this example so you can copy/paste and test for yourself.

MapX:
 Mapping Load * Inline [
 from, to
 1, A
 2, B
 ];
 
Mapping Load * Inline [
 from, to
 3, C
 ];

Data:
 Load RecNo() as Recid,
 ApplyMap('MapX', RecNo()) as Mapped
 AutoGenerate 5;

The resulting output looks like this, proving that 3/C has been added to the map.

Unlike implicit concatenation for standard tables, the fieldnames  need not be the same. This script will concatenate. Note the different fieldnames in the second load.

MapX:
 Mapping Load * Inline [
 from, to
 1, A
 2, B
 ];
 
Mapping Load * Inline [
 X, Y
 3, C
 ];

What if there is an intervening standard load?  Will concatenation occur?

MapX:
 Mapping Load * Inline [
 from, to
 1, A
 2, B
 ];
 
Fact:
 LOAD 1 as X AutoGenerate 1; 

Mapping Load * Inline [
 X, Y
 3, C
 ];

The answer is no, concatenation will not happen. The second Mapping Load will create a new invisible mapping table.

So if I  can’t name the mapping table in a Concatenate prefix, is there some other way to explicitly concatenate?  Turns out there is. This will work.

MapX:
 Mapping Load * Inline [
 from, to
 1, A
 2, B
 ];

Fact:
 LOAD 1 as X AutoGenerate 1;

MapX:
 Mapping Load * Inline [
 X, Y
 3, C
 ];

When naming the mapping table again with the same label. explicit concatenation will occur!  This is unlike a standard load where repeating a label results in  a new table with “-1” suffix  (when fieldnames don’t match).

In summary, you can add rows to a mapping table. Repeating the table label again will ensure you are adding,  whether there is an intervening standard load or not.

Now you may be wondering …can I do an implied Join? I think not.

On Jan 20, 2021 I’ll be sharing more scripting tips and techniques in my “Advanced Scripting” on-line course as part of the “Masters Summit at Home” series.   Join me there for more on topics like creating reusable script and maintaining optimized loads.

Share

CubeTester

When working on Qlik Sense performance issues I frequently find I want to measure the performance of specific expressions. I might want to know how variations of an expression may perform against each other.  In a slow chart with many measures I want calculation time individually for each measure to focus my efforts.  Or perhaps I’m just satisfying a general curiosity or trying to settle a bet.

You can measure the performance of expression variations by modifying the chart and measuring the overall chart response time with something like Chrome Add Sense or QS Document Analyzer.  That approach can get kind of clunky especially when you are focused on a subset of measures in the chart.

I prefer a more structured approach to testing expressions. The tool I reach for is CubeTester.

CubeTester is an open source Nodejs command line tool for testing the performance of Qlik HyperCubes (Dimensions and Measures).  The test specification is written in a json file as either a HyperCubeDef or the “simplified” Dimension/Measure syntax.

Here’s a sample test written in simplified syntax that tests three variations of a cube (chart) containing one Dimension and three Measures.

I’ll run  CubeTester specifying the file that holds this test:

node index.js test tests/columns.json

And receive this output:

There is no significant difference in performance between the variations. Importantly, I can also see that all three return identical  total values as well.

CubeTester supports two commands:

  • test : Run tests.
  • extract: Extract app charts into a test file.

There are a number of options that can be specified on the command line or in the test definition. See the readme for more information on available options.

in addition to testing variations or trying out a theory, here are some other cases where I’ve used CubeTester.

  • When working with a mashup where my HyperCube exists only in code, there is no chart to test.
  • In a slow rendering chart I can test individual measures, combinations of measures and non-data expressions (like color expressions) to find the culprit.

Using CubeTester I can quickly try out ideas and document my progress as I work through an issue. I’ve made some interesting discoveries!

Some notes:

  • Testing against a server uses certificates for authentication.  (Pull request welcome if you want more auth options).
  • Make sure you specify “wss” when using a server endpoint eg
    wss://your.server:4747
  • You’ll need to test with enough data to get calculation times of sufficient magnitude.  Two results of 5 milliseconds vs 7 milliseconds are not precise enough to draw conclusions from.
  • Calculation time is affected by the capacity of the target machine and what else is running.  I recommend to repeat tests until you see a stable pattern.  Use the –repeat option and take the lowest result from each repeat.

CubeTester is free to use. Have fun!

-Rob

 

Share

Data Browser Tricks

Summary: I demonstrate my latest “Data Browser” sheet for use in Qlik data modeling. Download here.

Today I want to share the latest version of my “Data Browser” sheet.

What’s a “Data Browser”? It’s my name for something you may already have. The ubiquitous “System Sheet” or sometimes “Profiler” where you may have listboxes or charts utilizing the system fields “$Field”, “$Table”, “$Rows”.  These fields are part of the shadow data model that hold useful metadata about the data loaded in your app.

Some very useful example profile sheets have been shared in the community over the years.

You can download the latest QlikView and Qlik Sense versions of my Data Browser here.

For QV, I just copy the objects into my new app in one go. For QS,  I either copy the charts one-by-one or if I’m lucky they are already in my template (who’s going to build the sheet copy extension?) .

I’ll do this walk through with the QV version, the QS version is similar.

When I make selections in $Field I can see values and frequency counts in a listbox. After selecting for example the “Sales” field, the values and frequency counts are shown in a listbox. I also have a  histogram and some descriptive statistics about the field values.

I can select field values and drill into data using  this sheet or my application sheets. For example, I might want to select the outlier high Sales value, then select Product in $Field to find out what Product is associated with this Sale, clear the Sales field to see what other Sales look like for this product and so on.

There are several properties such as Null Count (Information Density), field alpha/num content ($tags) that I can get from the built in Table Model Viewer.  The Viewer requires me to examine one field at a time. In the chart below I get an overview of those properties of interest.  Because it’s a chart, I can use sorting and selecting to focus.

Something I don’t get from the Table Viewer is the difference between numeric count and text count for a field.  I like to surface this problem (highlighted in yellow) early in my modeling.

One of my favorite features is the “Value Association” chart below. It’s likely a favorite because it took me a long time to work out the expression!

In  Table Viewer we use “Subset Ratio” to understand where we have connected and un-connected data in our model.  Subset Ratio is limited to reporting the relationship between key fields. It can’t tell me how data field  values in this table associate to data field values in other tables.  Subset ratio, like other stats in the Viewer, does not respect selections. For example, if I select a specific Customer, how many SalesReps are linked to the Customer?

The chart below (highlights added) covers all these use cases and can also surface problems in the model.

I’ll start by selecting a field central to my model, in this case “OrderID”.   I then sort by the “Pct” column.  This column represents the percentage of field values associated to the selected field,
“OrderID”.

In the green highlight, I’ve called out a Table “Sales” that has zero association with Orders. Something to look into.

In the orange callout, I can see that only 19% of my Employees are associated with Orders.  That might be a candidate for trimming the Employees table when loading.

In the red callout, I see something puzzling. Only 47% of my OrderDates are linked to Orders. That may be ok, I would need to review the data.  What looks not ok is that WeekDay Pct is also 47%. I would have expected something like 5/7 or 6/7. No fraction of weekdays would equal “47%”.  And there are 1092 values for WeekDay…something is off.

I’ll select WeekDay from the chart and examine the values in the listbox. Aha, the Weekday values were created incorrectly.  They were created with a Date() function instead of the correct WeekDay() function.

I used to hide the sheet before production but now I generally leave it in as I and others find it useful in production as well.

Besides the Table Viewer, another Qlik modeling tool I really like is Catwalk. If you haven’t used Catwalk I encourage you to check it out.  I won’t go in to explaining Catwalk as it has an excellent tutorial built in.

Do you have any favorite profiling or modeling tools to use with Qlik?

-Rob

Share

QViewer Version 4 is Here

QViewer, the ubiquitous QVD file viewer,  has received a major update. In addition to the fast and beautiful QVD viewing and filtering you are used to, V4 introduces “Folder View”. Folder View allows you to work with folders and subfolders of QVDs, viewing details and opening QVDs from the list.

Here’s what the new Folder View looks like:

 

You have a rich set of options for filtering, sorting and launching actions like viewing or compare.  Want to identify the differences between two QVDs?  Take a look at the  compare function:

This short video gives an overview of some of the Folder View features and capabilities.

Download QViewer V4 here.

And of course you still have fast viewing and filtering within a QVD in the File View:

 

QViewer is free to use with limits — 100k rows per QVD and 50 QVDs per Folder.  Purchase a low cost license to remove the limits and support my work.

Want to explore all the features of QViewer? Visit the help site.

If you have any feedback or enhancement suggestions., visit the EasyQlik Community site.

Happy Viewing!

-Rob

Share

Creating Temporary Script Associations

Summary: I review using Join, Lookup() and ApplyMap() as script techniques  to calculate using fields from multiple tables. I ultimately recommend ApplyMap().

Qlik charts  can calculate values on the fly, using fields from multiple tables in the model.  The associative model takes care of navigating (joining) the correct fields together.  Our expression syntax doesn’t  identify what table a field exists in — the associative logic takes care of this detail for us.

We may want to calculate a measure, for example, “Net Amount”, applying a business rule requiring fields from several tables:

Our expression to calculate “Net Amount” might look like this:

Sum(Amount) - 
RangeSum(
  Sum(Quantity * UnitCost), 
  Sum(Amount * Discount), 
  Sum(Amount * SalesTaxRate), 
  Sum(Amount * ExciseTaxRate)
)

There may be cases (such as performance) where we want to pre-calculate “Net Amount” as a new field in the script.  In script, we don’t have the magic associative logic to assemble the fields.  When a script expression is used to create a new field, all fields must be available  in a single load statement.  This is straightforward when all the required fields are in the same table.  But what do we do when the fields come from multiple tables?

Here are three approaches to solving the problem of calculating a new field using multiple tables in script.

  • Join
  • Lookup() function
  • ApplyMap() function

I’ll demonstrate deriving the same “Net Amount” calculation in the script.

JOIN

The Join option will require us to execute multiple joins to assemble the fields onto each Orders row and then finally do the calculation.  The script might look like this:

Left Join (Orders)
LOAD
 ProductId,
 UnitCost
Resident Products
; 
Left Join (Orders)
LOAD
 CustomerId,
 Discount,
 State
Resident Customers
; 
Left Join (Orders)
LOAD
 State,
 SalesTaxRate,
 ExciseTaxRate
Resident States
;

NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
   Quantity * UnitCost,
   Amount * Discount,
   Amount * SalesTaxRate,
   Amount * ExciseTaxRate
 ) as NetAmount
Resident Orders
;
// Drop the extra fields from Orders.
Drop Fields State, UnitCost, Discount, SalesTaxRate,ExciseTaxRate
From Orders
;

It’s a fairly good option.  It can be a lot of code depending on how many fields and tables we need to traverse. We need to be aware of “how many hops” between tables and may require intermediate joins (State field) to get to the final field (SalesTaxRate & ExciseTaxRate).

When using Join we need to ensure we have no duplicate keys that would mistakenly generate additional rows.

LOOKUP

Lookup() seems the most natural to me. It’s the least amount of code and it even sounds right: “look-up”.  It’s a one-to-one operation so there is no danger of generating extra rows.

It’s my least used option due to performance as we shall see.

Lookup takes four parameters  – a field to return, the field to test for a match, a match value to search for and the table to search.  Using Lookup() our script will look like this:

NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
   Quantity * Lookup('UnitCost', 'ProductId', ProductId, 'Products'),
   Amount * Lookup('Discount', 'CustomerId', CustomerId, 'Customers'),
   Amount * Lookup('SalesTaxRate', 'State', Lookup('State', 'CustomerId', CustomerId, 'Customers'), 'States'),
   Amount * Lookup('ExciseTaxRate', 'State', Lookup('State', 'CustomerId', CustomerId, 'Customers'), 'States')
 ) as NetAmount
Resident Orders
;

Note that for SalesTaxRate and ExciseTaxRate, the third parameter — the match value — is another Lookup() to retrieve the State. This is how we handle  multiple hops, by nesting Lookup().

It’s a nice clean statement that follows a simple pattern.  It performs adequately with small volumes of data.

Lookup does have a significant performance trap in that it uses a scan  to find a matching value.  How long to find a value is therefore dependent on where in the field the value is matched.  If it’s the first value it’s very quick, the 1000th value much longer, the 2000th value exactly twice as long as the 1000th. It’s a bit crazy making that it executes in O(n) time, for which I prefer the notation U(gh).

APPLYMAP

I like to think of the ApplyMap() approach as an optimized form of Lookup().  We first build mapping tables for each field we want to reference and then use ApplyMap() instead of Lookup() in the final statement. Our script will look like this:

Map_ProductId_UnitCost:
Mapping
Load ProductId, UnitCost
Resident Products
;
Map_CustomerId_Discount:
Mapping
Load CustomerId, Discount
Resident Customers
;
Map_CustomerId_State:
Mapping 
Load CustomerId, State
Resident Customers
;
Map_State_SalesTaxRate:
Mapping 
Load State, SalesTaxRate
Resident States
;
Map_State_ExciseTaxRate:
Mapping 
Load State, ExciseTaxRate
Resident States
;
NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
   Quantity * ApplyMap('Map_ProductId_UnitCost', ProductId, 0),
   Amount * ApplyMap('Map_CustomerId_Discount', CustomerId, 0),
   Amount * ApplyMap('Map_State_SalesTaxRate', ApplyMap('Map_CustomerId_State', CustomerId, 0)),
   Amount * ApplyMap('Map_State_ExciseTaxRate', ApplyMap('Map_CustomerId_State', CustomerId, 0))
 ) as NetAmount
Resident Orders
;

The mapping setup can be a lot of code depending on how many fields are involved. But it’s well structured and clean.

In the final statement, we are “looking up” the value using ApplyMap() and it performs very quickly.  ApplyMap uses a hashed lookup so it does not matter where in the list the value lies, all values perform equally.

We can re-structure and simplify the mapping setup and subsequent use with a subroutine like this:

Sub MapField(keyField, valueField, table)
// Create mapping table and set vValueField var // equal to ApplyMap() string.
 [Map_$(keyField)_$(valueField)]:
 Mapping Load [$(keyField)], [$(valueField)]
 Resident $(table);
 Set [v$(valueField)] = ApplyMap('Map_$(keyField)_$(valueField)', [$(keyField)]);
End Sub

Call MapField('ProductId', 'UnitCost', 'Products')
Call MapField('CustomerId', 'Discount', 'Customers')
Call MapField('CustomerId', 'State', 'Customers')
Call MapField('State', 'SalesTaxRate', 'States')
Call MapField('State', 'ExciseTaxRate', 'States')

NetAmount:
LOAD
 OrderId,
 Amount - RangeSum(
 Quantity * $(vUnitCost),
 Amount * $(vDiscount),
 Amount * $(vSalesTaxRate),
 Amount * $(vExciseTaxRate)
 ) as NetAmount
;
LOAD
 *,
 $(vState) as State
Resident Orders
;

Note the use of the preceding load to handle the nested lookup of State.   You could also modify the Sub to handle some level of nesting as well.

I typically use the mapping approach as I find it always gives accurate results (with Join you must be careful of duplicate keys) and generally performs the best, and importantly, consistently.

Whether you are new to Qlik or an old hand I hope you found something useful in reading this far.

-Rob

 

 

Share