Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
how can i use a field value in an expression, without having it as dimension in straight table??
if(match(mid(VALUE,1,6),$(=(CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', ')))),RGB(255,90,80))
TOTAL Qualifier... + Aggr Function
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!?
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!
Need to understand where you have used this expression? What is the dimension and expression of chart?
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.
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????
if(match(mid(VALUE,1,6),$(=(CONCAT(DISTINCT(chr(39)&FIELD_VALUES&chr(39)),', ')))),RGB(255,90,80))