Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Macros are Bad

There are several good reasons not to use macros in QlikView.

First of all, macros are run using Microsoft COM Automation. They will run client-side and work fine if you use the Desktop or the IE PlugIn, which both are Windows executables. But if you use the Ajax client, there are severe limitations since the macros now need to run on the server instead of on the client. For a server, there are both technical limitations and security implications that have forced us to restrict the capabilities of macros.


So, if you want to use the Ajax client, you should avoid macros.

Trigger.png

Secondly, a common beginner’s mistake is to use macros to build logic into QlikView; logic that almost always works better if QlikView’s own out-of-the-box functionality is used.  An example is assigning a value to a variable every time a selection is made: In other tools you would use a trigger and a macro, but in QlikView this is not needed: You should instead define your variable using an equal sign and the appropriate expression.

Further, many problems can be solved by using a smart data model instead of macros.

Macros often confuse the user. A user wants a predictable behavior and macros often do exactly the opposite: They create behaviors that exist in some places but not in other. This is counter-intuitive.

Finally, if the macro is long or complex, it will often push QlikView into an unstable situation. We have often seen this in documents sent to our support organization. The reason is probably a poor fit between the synchronous, sequential execution of a macro and QlikView’s internal asynchronous, multi-threaded computation.

From a development perspective, the macro feature is a secondary functionality. In the development process, it will rarely get first priority when compared to the “native” functionality: I.e. in the choice between keeping macro functionality unchanged and improving the out-of-the-box functionality, the QlikView development team will often choose the latter. This means that you can get a changed macro behavior when you upgrade your QlikView installation.

Macro1.png

Some facts around macros:

  • Single-Threaded? Macros are in their nature sequential – i.e. in a way single-threaded. But this is not the same as saying that all calculations started by macros are single-threaded. For instance, if a macro makes a selection that causes a chart to be calculated, both the logical inference and the chart calculation are multi-threaded. As they should be.
  • Clears cache? No. A macro does not clear the cache. (Unless you use the macro in the picture above...)
  • Clears the Back-Forward stack? No. A macro does not clear the Back-Forward stack.
  • Clears the Undo-Redo stack? Yes, if the macro contains layout changes, the Undo-Redo stack will be cleared.

So, I strongly recommend you don't use macros, unless it is in a Desktop environment. And there they should be short and concise! On a server you should instead use Actions.

There is however one thing that may confuse the user more than macros. And that is Triggers. If a macro or an action is started from a nicely labeled button, then the user will understand what happens.

But if the macro instead is triggered by some other event, e.g. the changing of a variable value, we have a very different situation. Then you will most likely create a non-intuitive behavior.

Macros are Bad, but Triggers are Worse.

HIC

See also

The Key to Heaven

55 Comments
pover
Honored Contributor

I completely agree.  I hardly ever use actions either, but the one time I did use them extensively some years back, I remember having trouble understanding the sequence in which a list of actions were executed.  The ascend and descend buttons to order a list of actions gave me the impression the actions were executed sequentially, but I remember having trouble doing things like first selecting a value, then assigning a resulting value to a variable, then clearing the selection, and making a another selection based on the same variable all in one action list.  Any insight on how actions are executed?

0 Likes
13 Views
luciancotea
Valued Contributor

As usual, great article! I think there will be a lot of comments around it because macros are the most popular discussion topic.

Everybody is different and everybody wants to differentiate. Custom behaviors, custom data export, emails, custom software integration, time saving automation tasks... all possible with macros.

Yes, macros are bad, but it's the necessary evil.

BTW, any words on the QlikView NEXT API? How the new changes are going to affect the third party tools (like NPrinting, QV Mail,...)?

13 Views
richard_pearce6
Valued Contributor

It's a shame but unfortunately fact....

Macros can be very useful though, especially when actions don't give you the flexibility.

Adding bookmarks done via a macro allows you the define the bookmark. Why are these basic parameter settings missing from the inbuilt action.....?

0 Likes
13 Views
Not applicable

Henric, I use macro's to define functions in the load script for calculations that are not available with QV functions. For example when I need to a newton-raphson optimisation (iterative process). And offcourse in the user interface to export multiple charts at once. 

0 Likes
13 Views
MVP
MVP

Hi Henric, Completely agree with all your points here.  I often post responses on the Community stating that to do that you will need to use a Macro - so don't do it.

Triggers, when used sparingly, can be very useful - but I have seen some real train wrecks where the developer has set a bunch of selections on the On Tab Change event, totally confusing the users.

Steve

0 Likes
13 Views
Employee
Employee

I agree with all of the above: Though BAD, macros are sometimes still necessary. And also I use them... However, I think it is important to set a base line - the initial approach must be not to use macros. They are the last resort.

@Richard Pearce: I agree. Actions should be able to do that.

@Mathias Vanden Auweele: You are right that you sometimes need to define your own functions - especially the iterative like Newton-Raphson, or to generate Poisson distributed random numbers (something which I have done using a macro function). I would just like to have another way than macros for that...

HIC

13 Views
Not applicable

Henric Cronström wrote:


I would just like to have another way than macros for that...

I agree!

0 Likes
13 Views
Employee
Employee

Karl Pover If you have a list of actions (e.g. in a button) then the actions will be executed sequentially, as you would expect. However, there are some caveats:

  1. An action does not always wait for the previous action to finish. E.g. two selections in a row - the second selection will interrupt the calculation of the first, just as two clicks in the UI.
  2. Variables are assigned to the entire list, before the first action is executed. So, you cannot calculate a variable in one step and then re-use it in a later step. The reason is that the action is executed on the client, but the variable is assigned on the server, so a list of actions would be much too "chatty" if we had assigned the variables before each action.

HIC

13 Views
brian_booden
Contributor

Steve,

There's nothing worse than an app full of OnChange triggers that reset selections when changing tabs.  That's the worst.  Triggers do have their place, but also need to be used with caution, especially from a navigational perspective.

Macros might provide extra functionality on occasion, but most of the time, i completely rule them out from the off due to compatibility issues with Ajax, as HIC rightly pointed out.  Most of the implementation we do nowadays are deployed using the Ajax client, so macros are just a no-go area in this regard.

Brian

13 Views
brian_booden
Contributor

Henric Cronström, thanks for stating this explicitly.  I have seen this behaviour several times with button Actions, and always wondered if i was doing something wrong when the Actions appeared to be triggering out of order, or even more confusingly, in a random order.  In future i'll just try an alternative method, rather than squeezing all the actions into one button!

0 Likes
13 Views
sudeepkm
Valued Contributor III

Hi Henric,

     Thanks a lot for sharing an insight on macros. We are not allowing our developers to use macro in our environment as we are using AJAX mode for our client access.

     However sometimes our clients demand certain features which are possible only using macros. We face a lot of difficulty in explaining our clients about the feature not being native to the product.

As an example :

QlikView has a Trigger Action type as "Print Object" which works fine in AJAX mode however similar feature "Print Sheet" is not supported in AJAX client. This puts us in a situation where we cannot provide satisfactory answers to our clients that why the Print Sheet does not work whereas a similar feature Print Object works fine in AJAX mode.

macros-are-bad.png

Another feature which our clients would like to see is the export to xls for multiple tables to multiple sheets of same xls output. (This is possible only using Macro).

Are there any plans for taking care of such UI utility type features in future. Please suggest. Thanks in advance.

Thanks and Regards,

Sudeep

13 Views
Not applicable

Hi Henric, Sudeep,

Thanks for the interesting text and comments above. To follow up on Sudeep's comments - when it comes to exporting charts/tables to Excel and Power Point QV simply does not have a good answer. The best indication for this is the extent of the online discussions around these topics in the QV community. The only way around this limitations is using Macros. I'm curious to hear Henric's thoughts on this particular point.

Thanks and Rgds,

Ami

0 Likes
13 Views
brian_booden
Contributor

I think it's fair comment that QlikView does not handle exporting very well in terms of formatted output in Excel specifically.  Of course, export to Excel is possible and easy enough, but if you want flexibility in terms of formatting, then it does either need to be macros or a 3rd party tool such as NPrinting or Report Manager.

It would interesting to hear from Qlik though, as to whether report formatting and output is something they are concerned with internally, or whether they will lean on existing solutions such as the above to provide the capability needed.

0 Likes
13 Views
pover
Honored Contributor

I have a customer that battled with macros to export multiple reports to Excel.  Once they found out about NPrinting and implemented it, they disabled all the macros and were much happier.  There are other tools out export tools out there and I don't think Qlik should invest time and resources in reporting.  I would rather see a QlikView.Next jammed pack with more innovative features that spread information in other more effective ways than an Excel spreadsheet.

13 Views
dvqlikview
Honored Contributor II

Thank you HIC. Nice post on Macros.

A Macro does what you tell it to do, not what you want it to do.

Cheers,

DV

13 Views
Not applicable

Oh, geez.. this reminds me of Djikstra's "GOTO Considered Harmful". Sweeping generalizations sweep a lot under the rug.

My firm uses a product "Goldmine" to track activity by sales and customer service. It puts a timestamp on each record of DD/MM/YY hh:mm, which is fine, but there are times when quick users can put two, sometimes three, entries in the same minute. When I ask QV to list out these activities with the time stamp, as you might imagine, I get "null" as a result. I had to find a way to get the seconds added to the time stamp.

Goldmine does encode the creation time, down to the second, in its "RECORDID" field. Here is the code in my macro: (note: the algorithm is GM's; I just reversed it)

Function secs(recid)

For I = 1 to 6

   Dummy = mid(recid,I,1)

   If Isnumeric(Dummy) THEN

         CharASCVal = ASC(Dummy) - 55

   Else

         CharASCVal = ASC(Dummy) - 48

   END IF

   CharVal = CharASCVal * 36 ^(7-i)     <- yes, that's an exponent

  DecryptDate = DecryptDate + CharVal

Next

FirstSec = DecryptDate/50 MOD 60

If FirstSec< 10 then

    secs = "0" + cstr(FirstSec)

ELSE

     secs = cstr(FirstSec)

END IF

END FUNCTION

I put this function in my load statement, and make a full time stamp from:

LOAD

     DATE#(Date(OnDate+OnTime,'MM/DD/YY hh:mm') & Secs(RECORDID),'MM/DD/YY hh:mmSmiley Frustrateds') As FullTime

This gets used in dozens of different QV files. If you can find a more efficient and time saving method of doing this rather than using a macro, I'd like to hear it. I always thought the entire purpose of functions and subroutines was to break code down into small, robust units. Since I'm unaware of any ability to do this in QV scripts, I'll continue to use macros where warranted.

However, I do agree with someone above - we tried exporting files with macros. When we switched to nPrinting, that all stopped, and the users are much happier.

0 Likes
13 Views
barryharmsen
Contributor II

I think macros are bad when used in user-facing applications. During development, I think macros are great!

I'm a lazy person (though when I'm speaking with clients I rephrase it as "efficient" ) and I dislike repetitive work. That is why I love to use macros (and subroutines) during development to automate frequently occurring tasks that require a lot of manual work.

One such example is the "ad-hoc analysis" object, the one where you pick your dimensions and measures from a list and the result is shown in a straight table/pivot table/etc. My clients love those, but they are IMHO a real hassle to build. Typing in dozens of show/hide conditions takes a long time and is mind-numbing. So, I created a macro that does it for me. The client wants to change something? Just delete the object and regenerate it.

Of course, this requires a little more effort upfront and it doesn't make sense for every task, but I generally find that the time-savings well outweigh the initial time investment.

Regarding NPrinting, I absolutely love the product, but (as I understood it) they're using the same methods/type library to export data from QlikView. Instead of using your own macros you're just using someone else's. At their pricing it is still a great deal though, and IMHO having someone else build your tools is an even more "lazy" approach, so that definitely gets my approval

13 Views
Employee
Employee

@ Barry Harmsen We are basically saying the same thing... Note the "...unless it is in a Desktop environment" in my post. Yes, I use macros too, but just as you say - they are bad in user-facing applications.

@ Kevin Bertsch No, I do not see these views as sweeping generalizations. Macros imply a number of drawbacks and problems, some of which I mention. But of course they have advantages too. Barry makes a good point when he points out the time-saving aspect in the development phase. And as always, you need to weigh pros and cons. The purpose of the blog post was to make people aware of the drawbacks of macros.

(...and I bet you I can write that macro function of yours using native QlikView functions only.  )

@ Brian Booden Concerning reports and NPrinting: Yes, we are concerned, but the way we see it is that reporting is better done by our partners than by us. So we welcome partners like NPrinting. (NPrinting, by the way, uses our COM automation interface in our OCX module. In other words: From a code perspective a single user, using something equivalent to the Desktop. Which I think is OK.)

HIC

0 Likes
13 Views
Not applicable

Henric,

I have no doubt you can ‘recreate’ the function. (My knowledge of QV is a mere evanescent shimmer

on my cerebral cortex compared to yours. )

Now, recreate it every time in two dozen different QV files, then,

when the function changes for any reason, go back and maintain it.

I stand by my earlier assertion: breaking code down into robust, re-usable chunks is good practice.

If QV had any type of user defined function/sub-routine in its scripting tool, I wouldn’t need the

macro, but AFAIK, it doesn’t.

Cheers,

Kevin Bertsch

Data Analysis and Reporting

Ecustoms

0 Likes
13 Views
Employee
Employee

I totally agree both with your view on "robust and re-usable chunks" and the need for user defined functions. Further, most my arguments against macros pertain to "user-facing" situations, as Barry put it. And your example isn't user-facing...

So, bottom line: There are drawbacks. Be aware of them. Then use macros where appropriate.

HIC

0 Likes
13 Views

Karl Pover

In addition to Henric's explanation of the Action sequence for variables, I've found that I can't update a field and reference the updated field value in a subsequent action. It's like the data available to the actions is a read only copy. I've never figured out if this is because the value expressions are pre-calculated before the actions are executed or what.

For example, consider this chain of "Select In Field" actions:

1. Field = F2, Value = max(F1)

2. Field = F3, Value = max(F2)

F3 will get set with whatever max(F2) was before Action#1 was executed.

13 Views
athompsonhp
New Contributor III

Although you have some good arguments BUT I disagree in your strong use of BAD.  Macros offer developers a means to write Functions which are NOT included in the native QlikView.  I have written some very complex functions which are confidential and cannot be shared.  If I was not able to do this development then QlikView would be very limited.  Actually it is the Macro functionality of QlikView that separates QlikView from the other BI tools!!  So I would say "Practice Safe Macros" and not "Macros are Bad".

0 Likes
13 Views

Kevin Bertsch

"If QV had any type of user defined function/sub-routine in its scripting tool, I wouldn't need the

macro, but AFAIK, it doesn’t."

QV Script does support subroutines well. Also $include files which allow you to pull in script snippets from external files. Provides great re-usability.

QV Script does not currently support writing true Functions in the script language. It's one of the features I keep lobbying for. Variables with parameters are sort of a User Defined Function that can be used in a script, but they are they are limited to a single expression.

13 Views
Employee
Employee

Andy

I agree with everything you say. The use of "Bad" is provocative - but that was also the intention... Also, "practice safe macros" and "avoid user-facing macros" are certainly more nuanced versions of the same message.

The reason for the blog post is that we at Qlik, fairly often see examples of excessive macro usage; macros that are unnecessary since they just mimic QlikView's native functionality; macros that are unmanageable since they are long and complex; macros that degrade QlikView performance and macros that destroy the simplicity that our users love. And sometimes the files are sent to our support with a question why it doesn't work...

To make a point, I deliberately chose "Macros are Bad" as title.

HIC

0 Likes
13 Views
barryharmsen
Contributor II

Henric Cronström wrote:

To make a point, I deliberately chose "Macros are Bad" as title.

Oh Henric, you provocateur

13 Views
luciancotea
Valued Contributor

Told ya there's going to be a storm of comments

13 Views
jason_michaelid
Honored Contributor II

Barry Harmsen, for your customized table, check out my post on an alternative and much less time-consuming way to do this - http://community.qlik.com/docs/DOC-6046

Hope it helps,

Jason

0 Likes
13 Views
thornofcrowns
Valued Contributor II

Badly written code of any stripe is BAD, IMHO.

However, my main gripe is that if I want some control over printing, I need to have some code behind the scenes as it is plain that QV view of " ... the way we see it is that reporting is better done by our partners than by us." isn't going to help me any time soon.

I know, I know, QV isn't a reporting tool, etc., etc., but that viewpoint isn't universally accepted by users, it's sad to say.

0 Likes
13 Views
amonjaras
Contributor II

Thank you Henric and everyone for a great discussion that may not have been so active with a different title .

I advocate a "responsible" use of macros by making sure the consultants / developers / clients understand how powerful they are. They need to be aware that they can make an application possible, like Mathias Vanden Auweele points out, or they can completely ruin the associative experience for the end-user.

Needless to say, we strive to make sure that every macro is absolutely necessary.

0 Likes
13 Views
Not applicable

Rob, if you get a minute, could you please point me to the QV subroutine documentation, because I'm not aware of how to do it.

And since no one apparently got my Djikstra reference, here's a link to "GOTO Considered Harmful"

https://www.cs.utexas.edu/users/EWD/ewd02xx/EWD215.pdf

0 Likes
13 Views