Showing Only RAG values

[bobbydave]

I have 2 columns, X = text and Y = values.

Column Y brings in both Text and Numbers.

What I am trying to achieve  is for only the Text to appear which is R A G (Red, Amber, Green).

Expression = Only({<Value={'R','A','G'}>} Value)

This is all good in that it does not add any other values (shows them as null) and shows R, A,G.

I dont however want any values showing apart from R, A, G. so X shows the corresponding values of R, A, G and no other values appear on the rest of the table.

What I appears

x	y
Text	1
Texta	R
Textb	A
Textc	4
Textd	G

What appears after I use the expression     = Only({<Value={'R','A','G'}>} Value)

This obviously removes the values

x	y
Text
Texta	R
Textb	A
Textc
Textd	G

What I am trying to achieve

X	Y
Texta	R
Textb	A
Textd	G

I am fairly new to Qlikview and trying to get my head around set analysis.

Any help appreciated.

[simondachstr]

Check the option to suppress null values in your chart.

= Only({<Value={'R','A','G'}>} Value) will always evaluate for each row, but will evaluate to null() if the letters are not available in Value. Therefore you'll need to suppress nulls or hide them. Another option is to add a calculated dimension (not recommended as its slower) using =if(match(Value,'R','A','G'),X, null()) . this makes sure that the null dimension is not calculated (but will again be displayed if you choose so in the chart properties.

[bobbydave]

Ah nuts, bloody rookie mistake.

That was exactly it.

However, I have 6 sheets in an excel sheet that I am working off of.

Sheet 2 should be bringing in the R,A,G values but its going through the 6 sheets and bringing all R,A,G values.

Is there something I can call in my expression to call only sheet 2 values?

[simondachstr · Accepted solution]

Uhm yes, but that's a different question.

When specifying the Excel source you can add the table is property:

LOAD *

FROM

Excelfile.xlsx

(ooxml, embedded labels, table is Sheet2);

If my post has helped you please mark it as the correct answer to help others having a similar issue quickly find a solution to their problem.

[bobbydave]

Yes, I have all 6 sheets loaded.

All 6 sheets have the same column name purposely as I need to separate them out in to different business needs and to be able to call.

I will have 6 charts displaying these in a  chart and I am trying to load sheet 2 with

Right now, i have sheet 1 loading all data, sheet 2 should only load the R,A,G  values but because there is RAG values in all 6 sheets, I am only trying to bring in the Sheet 2 values.

[simondachstr]

Again, try specifying Sheet2 in the load statement in the script:

When specifying the Excel source you can add the table is property:

LOAD *

FROM

Excelfile.xlsx

(ooxml, embedded labels, table is Sheet2);

[oscar_ortiz]

I think what you are wanting to do is specify a value in your table as a source.

LOAD

     'Sheet2' as Source,

*

FROM

Excelfile.xlsx

(ooxml, embedded labels, table is Sheet2);

Then add to your expression:

= Only({<Value={'R','A','G'}, Source={'Sheet2'}>} Value)