topic Chart expression - picking a value from a table in QlikView

Chart expression - picking a value from a table

googel84 — Fri, 20 Mar 2015 18:15:43 GMT

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?

Re: Chart expression - picking a value from a table

Gysbert_Wassenaar — Fri, 20 Mar 2015 18:39:30 GMT

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'))

Re: Chart expression - picking a value from a table

googel84 — Mon, 23 Mar 2015 09:44:55 GMT

Unfortunately, Aggregated color does have repeated values.

Thanks anyway.

Re: Chart expression - picking a value from a table

Gysbert_Wassenaar — Mon, 23 Mar 2015 10:14:03 GMT

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.

Re: Chart expression - picking a value from a table

googel84 — Mon, 23 Mar 2015 10:36:55 GMT

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.

Re: Chart expression - picking a value from a table

Gysbert_Wassenaar — Mon, 23 Mar 2015 10:39:21 GMT

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