topic Re: Script line with EVALUATE in QlikView

Script line with EVALUATE

evan_kurowski — Sat, 07 Apr 2018 14:06:57 GMT

Hello Qlik community,

In using EVALUATE during scripting to assign color numbers to the numeric side of a dual field, the sequencing of reloads went from working to broken, when the reload was moved from desktop to server.

In working with this syntax in the past, I had expected it to break on the server (not sure why it breaks, but it had at least consistently done so).

What behaved differently this time, was that the syntax stayed broken once reloaded back on desktop. In the past, returning to the desktop construct was enough to restore functionality.

1st pass: Working - when reloaded on desktop

2nd pass: Broken - when reloaded by a QMC task

3rd+ passes: Broken and stays broken when reloaded on desktop

Dual(Replace([CATEGORY],'Sales','Marketing'),Num(evaluate( ApplyMap('MAP_CATEGORY',Replace([CATEGORY],'Sales','Marketing'),'RGB(220,220,220)')))) AS [CATEGORY]

When this expression is run on the server, 1 of the several possible values in [CATEGORY] would return null (though others would still show), but then it would stay null until a backup copy was retrieved and rolled back an entire version (no code changes). The backup would perform fine on desktop, 1 pass through the QMC and it is broken, and then stays broken on desktop.

The ApplyMap was attaching color variables, either RGB or predefined:
RGB(#,#,#)
$(vColorVar_Blue)

Any thoughts on what could cause this?

Re: Script line with EVALUATE

marcus_sommer — Mon, 09 Apr 2018 12:59:08 GMT

I don't know why this happens but a possibility might be different releases between server and desktop. Here is a quite similar approach with an additionally field but without dual() and evaluate() which might be more stable in your environment: Colors in charts.

- Marcus

Re: Script line with EVALUATE

Peter_Cammaert — Mon, 09 Apr 2018 13:16:42 GMT

I always thought that colors and return values from color functions were something special. Not text, not numerical and neither a dual. To be handled with care.

Re: Script line with EVALUATE

marcus_sommer — Mon, 09 Apr 2018 13:37:18 GMT

I think it worked because they will be ( always ? ) converted into numbers:

- Marcus

Re: Script line with EVALUATE

evan_kurowski — Mon, 09 Apr 2018 13:53:44 GMT

Hello Marcus, thank you for replying.

Yes, for consistency's sake, it does seem like externalizing additional dimensional information as a leaf table connected to the original dimension field is the safest way to go.

I've experimented quite a bit with trying to compact the data model by packaging colors and sort ordinals into the numeric side of dual fields. This can yield tantalizing results within desktop, but often goes awry on server migration.

Sometimes the interpretation changes, other times the dual field starts requiring more specific instructions for understanding which half is in context

I.e. a sort order syntax that might originally work:
Max({1} DIMN)

later requires an explicit numeric side extraction:
Max({1} Num(DIMN))

Some apps with a lot of these "metadata" type leafs get that cilia feel, and it looks like that's just fine.

Re: Script line with EVALUATE

evan_kurowski — Mon, 09 Apr 2018 13:58:31 GMT

Yeah, this also reminds me of a technique for possible future exploration/testing. If we skip the RGB(), ARGB(), or color-variable, and just pack a dual with the Dual(String, color numeric value) would things be more consistent? I'm wondering if it is the color-expressions/variables making that conversion to numeric that trips things up. (except as mentioned in prior post, sometimes there are desktop v. server issues with dual interpretation even when the numeric side is packed with tidy sort ordinal integers).

Re: Script line with EVALUATE

marcus_sommer — Tue, 10 Apr 2018 14:27:40 GMT

I did a more close look on the meachanics behind it and I think it should work both in server and client and even over several different releases because the from Qlik used color-logic isn't new in any way and there should be nothing changed within the last years.

Nevertheless a potential cause could be the evaluate-function and any invalid and/or NULL values within the color-index. By applying colors to charts invalid colors will be ignored and insetad default colors will be used. I don't know how the check on valid/invalid colors happens but I could imagine that it's not just applied on row-level else it could be in some way a more global check and then the least error will lead to the default colors - any maybe this is also persistent through the storing of the qvw (I think it shouldn't but I wouldn't exclude this possibility).

This meant applying a working error-handling might be helpful. Regarding to this you could also replace the rgb() with the color-index. An example and more backgrounds to this topic could you find here: Colors in Qlik.

- Marcus

Re: Script line with EVALUATE

evan_kurowski — Fri, 20 Apr 2018 14:22:12 GMT

This topic got more coverage in Marcus's thread, but for reference adding illustration of a few techniques for packaging dimensional attributes here...

//Leaf tables

FOR EACH Dimn in 'A','B','C','D','E','F','G'

[DIMN_$(Dimn)_ATTRIBUTES]:
NOCONCATENATE LOAD
1 AS DIMN_$(Dimn),
1 AS DIMN_SORT_$(Dimn),
1 AS DIMN_COLOR_$(Dimn)
AUTOGENERATE(1);
NEXT

[FACT]:
LOAD
1 AS DIMN_A,
1 AS DIMN_B,
1 AS DIMN_C,
1 AS DIMN_D,
1 AS DIMN_E,
1 AS DIMN_F,
1 AS DIMN_G,
1 AS FACT_1,
1 AS FACT_2,
1 AS [FACT_ETC...]
AUTOGENERATE(1);

//Dimension attributes stored in fact table

[FACT]:
LOAD
1 AS DIMN_A,
1 AS DIMN_B,
1 AS DIMN_C,
1 AS DIMN_D,
1 AS DIMN_E,
1 AS DIMN_F,
1 AS DIMN_G,
1 AS FACT_1,
1 AS FACT_2,
1 AS [FACT_ETC...]
AUTOGENERATE(1);

FOR EACH Dimn in 'A','B','C','D','E','F','G'

JOIN(FACT)
LOAD
1 AS DIMN_$(Dimn),
1 AS DIMN_SORT_$(Dimn),
1 AS DIMN_COLOR_$(Dimn)
AUTOGENERATE(1);

NEXT

//Dual encapsulation of 1 set of dimensional attributes

[FACT]:
LOAD
Dual(1,1) AS DIMN_A,
Dual(1,1) AS DIMN_B,
Dual(1,1) AS DIMN_C,
Dual(1,1) AS DIMN_D,
Dual(1,1) AS DIMN_E,
Dual(1,1) AS DIMN_F,
Dual(1,1) AS DIMN_G,
1 AS FACT_1,
1 AS FACT_2,
1 AS [FACT_ETC...]
AUTOGENERATE(1);