topic Re: Rangecount in QlikView

Rangecount

yvonne-c — Wed, 24 Feb 2016 11:31:57 GMT

I am having an issue getting rangecount to work

I am using the following expression to count incidences across multiple columns which gives me the results below

Sum(RangeCount(L1,L2,L3,L4,L5,L6,IA,IB,IC,M1,M2,M3,M4,M5,M6))

Les	Count
OT	5
-	3
WLD	2
BR	1
Total	11

It works fine until there are more than one 'Les' on a row - then they are totalled on the '-' row.

I want to count each 'les' individually so the results should be as follows

Les	Count
OT	5
WLD	3
BR	2
IB	1
Total	11

An example of my data is attached

Any ideas?

Thanks

Re: Rangecount

hic — Wed, 24 Feb 2016 13:18:34 GMT

How is "Les" defined? I suspect that the definition is made so that NULL is returned when there are several values.

HIC

Re: Rangecount

yvonne-c — Wed, 24 Feb 2016 13:22:31 GMT

Les is a dimension

Re: Rangecount

hic — Thu, 25 Feb 2016 08:50:45 GMT

Yes of course it is. But how is it defined? What formula have you used?

Re: Rangecount

yvonne-c — Thu, 25 Feb 2016 09:28:24 GMT

Sorry I'm not sure what you mean!

My list of 'Les' are stored on a separate table?

Re: Rangecount

hic — Thu, 25 Feb 2016 10:52:20 GMT

I would use the following Load statement to load the data. This will define a field called "Les".

Crosstable (Column,Les)
LOAD
RecNo() as ID,
L1,L2,L3,L4,L5,L6,IA,IB,IC,M1,M2,M3,M4,M5,M6
FROM [example.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Then I would create a chart with "Les" as dimension and Count(ID) as expression.

HIC

Re: Rangecount

yvonne-c — Thu, 25 Feb 2016 20:01:19 GMT

Thanks, that's worked for me