Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Use field value in expression, without having it as dimension

Dear Community,

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

7 Replies
MK_QSL
MVP
MVP

TOTAL Qualifier... + Aggr Function

Anonymous
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
MVP
MVP

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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
MVP
MVP

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