Chart expression - picking a value from a table

[googel84]

Hi, is there a way to query a table from within a chart expression? Please note that the table cannot be linked to the rest of the model (it's a data island)

I managed to to that by means of a couple of variables (produced with concat) and match and subfield function.

TAB1:

LOAD

  FLG_COL_20 & FLG_COL_40 & FLG_COL_55 as [Color key],

  FLG_COL_20 as [Color (ph. 20)],

  FLG_COL_40 as [Color (ph. 40)],

  FLG_COL_55 as [Color (ph. 55)],

  FLG_COL_POS as [Aggregated color],

  rowno() as [Row number]

FROM $(vQVDPath)\TAB_COLORS.qvd (qvd);

LOAD

  chr(39) & concat([Color key],chr(39)&','&chr(39),[Row number]) & chr(39) as ColorKeyConcat,

  chr(39) & concat([Aggregated color],',',[Row number]) & chr(39) as AggregatedColorConcat

RESIDENT TAB1;

LET vColorKeyConcat = fieldvalue('ColorKeyConcat',1);

LET vAggregatedColorConcat = fieldvalue('AggregatedColorConcat',1);

This may be put in a text object.

=subfield($(vAggregatedColorConcat),',', match('ARR',$(vColorKeyConcat)))

Is there a simpler way?

[Gysbert_Wassenaar]

If the values of Aggregated color and Color key are unique then the values should get the same field index value in their symbol tables. So maybe: fieldvalue('Aggregated color',fieldindex('Color key','ARR'))

[googel84]

Unfortunately, Aggregated color does have repeated values.

Thanks anyway.

[Gysbert_Wassenaar]

So the example value 'ARR' could be related to multiple colors? But match('ARR',$(vColorKeyConcat)) will always return the first match. So, either that's not always a correct result, or you only need the first match anyway and can add a where not exists(FLG_COL_POS) to your load statement so you only have unique combinations.

[googel84]

No, "ARR" represents the combination of three colors (orange - A - red - R - and again red - R) which rolls up to "R" in Aggregated color; but, on the other hand, "R" may be the rolled up value for many other combinations of colors.

The solution I suggested in the original post works fine, but there may be a function that does the job better.

Thanks.

[Gysbert_Wassenaar]

Ok, you lost me. Please post some sample data.