Grouping

[tracycrown]

Dear all

Kindly advise the solution for the issues below:

Q1. Why 10 is in Range 1-2 of column 2 & 9 is missing ?
Q2. Why total number in column 3 cannot be computed ?. 

Correct Answer :

Grade Range	Grade	Total Number
1 - 2	1	3
	2	2
3 - 4	3	2
	4	8
5 - 6	5	2
	6	4
7 - 8	7	1
	8	4
9 - 10	9	3
	10	1

Thank you so much, Tracy

[edwin · Accepted solution]

its because Grade is loaded as text.  there are a couple of ways to address this:
use 

=if(num#(Grade)>=Lower and num#(Grade)<=Upper,Grade)

  or during load 

load
num#(Grade) as Grade,
...

[edwin · Accepted solution]

or you can fix your original data source, maybe it is configured as text or there's a space or something

[tracycrown]

Dear Edwin

Thank you so much for your quick response and best advice. I tried to modify it at source but it does not work, please advise ?.

CrossTable(num#(Grade), AData)
LOAD * FROM
[Test-1.xlsx]
(ooxml, embedded labels, table is Analysis);

Thank you very much, Tracy

[edwin]

as i said you can address this in load script, add the conversion 

num#(Grade) as Grade

[edwin]

you will of course enumerate your fields and not use * 

[tracycrown]

Thank you very much for your kind advice.