Use field value in expression, without having it as dimension

[Anonymous]

Dear Community,

how can i use a field value in an expression, without having it as dimension in straight table??

[MK_QSL]

TOTAL Qualifier... + Aggr Function

[Anonymous]

In detail i need to check if a variable of an column matches a set of Field values to change a color.

=if(match(mid(VALUE,1,6),total(FIELD_VALUES)),RGB(255,90,80))

total is not working here!?

[Oleg_Troyansky]

Theoretically, there is nothing to stop you from doing that. Practically, you need to know what to expect, logically. I'll give you two examples:

1. Chart per Product, when Color is an attribute of a Product. In this case, something like this is perfectly fine, because there is always only one Color per Product:

IF(Color = 'Blue', ...)

2. The same expression in a chart per Customer would return a NULL value because there are multiple Colors per Customer. In this case, you'd have to use AGGR to force the calculation at a different level of detail. For example:

AGGR(

     IF(Color = 'Blue', ...),

     Customer, Color)

This calculate will be technically correct, because the Color will be unique within an AGGR that has the field Color as a Dimension.

cheers,

Oleg Troyansky

Come and learn AGGR and Set Analysis with me at the Masters Summit for QlikView!

[MK_QSL]

Need to understand where you have used this expression? What is the dimension and expression of chart?

[Anonymous]

Straight table with dimension values A-C, one value C need to be highlighted if it matches a set of values of an dimension D that is not part of this table. So the expressions is inside Dimension C as background color expression.

[Anonymous]

I think i am now really close to solve this problem:

to get the multiple row values in one i use

CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', ') => As a result i get = '123, '234'

When i use this result directly inside if(match(mid(VALUE,1,6),'123, '234'),RGB(255,90,80)) = It works

But when i use the function above that produces this result it does not work!

if(match(mid(VALUE,1,6),(CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', '))),RGB(255,90,80))

What i am making wrong????

[MK_QSL · Accepted solution]

if(match(mid(VALUE,1,6),$(=(CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', ')))),RGB(255,90,80))