topic Showing Only RAG values in QlikView

Showing Only RAG values

bobbydave — Wed, 19 Nov 2014 15:39:04 GMT

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.

Re: Showing Only RAG values

simondachstr — Wed, 19 Nov 2014 15:44:30 GMT

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.

Re: Showing Only RAG values

bobbydave — Wed, 19 Nov 2014 15:49:16 GMT

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?

Re: Showing Only RAG values

simondachstr — Wed, 19 Nov 2014 15:55:27 GMT

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.

Re: Showing Only RAG values

bobbydave — Wed, 19 Nov 2014 16:06:37 GMT

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.

Re: Showing Only RAG values

simondachstr — Wed, 19 Nov 2014 16:17:45 GMT

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

Re: Showing Only RAG values

oscar_ortiz — Wed, 19 Nov 2014 16:24:00 GMT

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)