Solved: Nested variables are broken? - Qlik Community

mmarchese · ‎2017-11-06

I have an app that I made in Qlik Sense Desktop, and it worked 100% fine there. Now that we are paying to run Qlik Sense on an on-premises server, I am trying to get this app moved over to it and having great difficulty. In the desktop version, I could compose variables from other variables, and it worked just fine, like this:

Script:

Set v_uptime = "Sum({<Status={'Running'}>} m_minutes)";
Set v_plannedUptime = "Sum({<Status-={'Planned Down'}>} m_minutes)";
Set v_availability = "$(v_uptime) / $(v_plannedUptime)";

What I would see for the variable v_availability from the app side was this, as expected:

Sum({<Status={'Running'}>} m_minutes) / Sum({<Status-={'Planned Down'}>} m_minutes)

Now that I'm no longer using the desktop version, this is what I see on the app side instead:

Sum({<Status={'Running'}>} m_minutes) / Sum({<Status-={''Planned Down''}>} m_minutes)

Note how it inserted an extra pair of quotation marks around 'Planned Down', breaking the formula and killing my charts and graphs.

I've noticed that each time you nest a variable inside another one, you get an extra pair of quotes. Here's an example:


Set v_uptimeSorters = "Sum({<Status={'Running'}, [Machine Type]={'Sorter'}>} m_minutes)";
Set v_plannedUptimeSorters = "Sum({<Status-={'Planned Down'}, [Machine Type]={'Sorter'}>} m_minutes)";
Set v_availabilitySorters = "$(v_uptimeSorters) / $(v_plannedUptimeSorters)";

Set v_piecesSorters = "Sum({<[Machine Type]={'Sorter'}>} m_goodCount)";
Set v_idealPerfPiecesSorters = "Sum({<Status={'Running'}, [Machine Type]={'Sorter'}>} m_idealRate * m_minutes)";
Set v_performanceSorters = "$(v_piecesSorters) / $(v_idealPerfPiecesSorters)";

Set v_qualitySorters = "1";

Set v_OEESorters = "$(v_performanceSorters) * $(v_availabilitySorters) * $(v_qualitySorters)";

I tried removing the variable-creating code from the script, re-loading the data, deleting all the variables from the app, putting the variable-creating code back into the script, and re-loading the data. This did not fix the problem and produced 2 more glitches!

The first new glitch is that some formulas now appear incomplete, like this:

Set v_piecesRollers = "Sum({<[Machine Type]={'Roller'}>} m_goodCount)";
Set v_count = "($(v_piecesRollers) + Sum({<[Machine Type]-={'Roller'}>} m_cycles))";

The second new glitch is that some formulas are totally missing and replaced with asterisks, like this:

What on earth is going on here?! It would appear that Qlik's variable system is broken in the server version, but I'm trying not to jump to that conclusion and be open to the idea that I'm somehow doing something wrong here.

rwunderlich · ‎2017-11-06

Interesting. The doubling of the single quotes seems to be QS trying to be helpful by escaping the single quotes. I can reproduce your example and problem in QS Desktop Sept 2017.

SET does not do evaluation as LET does, but it does do DSE (substitution for $()). I note your use of " around your set values. The doc is unclear on this. It's shown in the examples with no explanation.

However...SET is very greedy, it will grab everything up to the semicolon. I never use quotes around my string regardless of it's contents. So you can code your statements like this without the outside quotes.

Set v_uptime = Sum({<Status={'Running'}>} m_minutes);

Set v_plannedUptime = Sum({<Status-={'Planned Down'}>} m_minutes);

Set v_availability = $(v_uptime) / $(v_plannedUptime);

This gives the desired result in both QV and QS.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

View solution in original post

rubenmarin · ‎2017-11-06

Hi Michael, seems the $-expansion is not working, this usually tries to calculate the expression, an inside the script it returns null(), you can try using a different character and a replace, ie:

Set v_piecesRollers = "Sum({<[Machine Type]={'Roller'}>} m_goodCount)";

Let v_count = Replace(Replace('#(v_piecesRollers) + Sum({<[Machine Type]-={^Roller^}>} m_cycles)', '#', '$'), '^', Chr(39));

Using Let needs the use of simple quotes, so simple quotes needs to be replace by another character (in this case '^'), and then replaced again with simple quote. Chr(39) is the code for a simple quote.

mmarchese · ‎2017-11-06

I understand your workaround and may give it a try - thanks. However, I'm very curious: does $-expansion not work in general on Qlik Sense Server? Or is this abnormal? Do you think this points to something being wrong with our installation?

rubenmarin · ‎2017-11-06

I'm more used to QlikView, and in QV it's normal, loading it from excel or any other source and then assigning it to a variable seems to avoid the auto-calculation of $(.

Mainly the idea is to avoid write '$(' in script when you want to keep the text as it was written, not trying to evaluate the content.

rwunderlich · ‎2017-11-06