Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Tags (1)
1 Solution

Accepted Solutions
MK_QSL
Not applicable

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

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

7 Replies
MK_QSL
Not applicable

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

TOTAL Qualifier... + Aggr Function

biob_stein
Not applicable

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

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

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

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

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

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

biob_stein
Not applicable

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

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.

biob_stein
Not applicable

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

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

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

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