topic Re: Use field value in expression, without having it as dimension in QlikView

Use field value in expression, without having it as dimension

Anonymous — Thu, 23 Apr 2015 15:43:29 GMT

Dear Community,

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

Re: Use field value in expression, without having it as dimension

MK_QSL — Thu, 23 Apr 2015 15:46:02 GMT

TOTAL Qualifier... + Aggr Function

Re: Use field value in expression, without having it as dimension

Anonymous — Thu, 23 Apr 2015 15:51:33 GMT

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!?

Re: Use field value in expression, without having it as dimension

Oleg_Troyansky — Thu, 23 Apr 2015 15:55:01 GMT

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!

Re: Use field value in expression, without having it as dimension

MK_QSL — Thu, 23 Apr 2015 15:55:43 GMT

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

Re: Use field value in expression, without having it as dimension

Anonymous — Thu, 23 Apr 2015 15:59:24 GMT

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.

Re: Use field value in expression, without having it as dimension

Anonymous — Fri, 24 Apr 2015 06:50:42 GMT

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

Re: Use field value in expression, without having it as dimension

MK_QSL — Fri, 24 Apr 2015 08:03:05 GMT

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