max

[#1]

I have table with following data

Name	Province	Sales
Bob	ON	200
Mike	ON	500
Bruce	ON	6000
Bob	BC	2000
Mike	BC	122
Bruce	BC	367
Craig	ON	3400
Craig	BC	1200

1. How can you show only max sales for each of the name regardless of the province.
2. How can you tell in Qlikview if fields is being read as number or text.

[maxgro]

max(total <Name> Sales)

from online help

IsNum( expr)

Returns -1 (true) if the expression can be interpreted as a number, otherwise 0 (false).

IsText(expr)

Returns -1 (true) if the expression has a text representation, otherwise 0 (false).

[#3]

for me both istext & isnum returning 0

How do i put the max into form of where clause.

[#4]

Ok let me ask this quesiton differently

I want to filter all the data that is not max while i am loading the data so it only loads the max records and drops the other ones.

so

Table:

Load

Name,

Province,

Sales

from temp.qvd

where    ..................   filter all the records without the max value

[maxgro]

I supposed in a chart, like this

[#6]

Data resides randomly in qvd. I want to restrict in during data load instead of loading all the data and then showing max value..

[maxgro]

x:

load * inline [

Name, Province, Sales

Bob, ON,200

Mike, ON, 500

Bruce, ON, 6000

Bob, BC, 2000

Mike, BC, 122

Bruce, BC, 367

Craig ,ON, 3400

Craig ,BC, 1200

];

y:

noconcatenate

load

Name, Province, Sales

Resident x

where peek(Name) <> Name

order by Name, Sales desc;

drop table x;

[#8]

Hello, can you please show what you expect to get into the table after the load with the filter?

[tresesco]

Try like:

Input:

load * Inline [
Name,Province,Sales
Bob,ON,200
Mike,ON,500
Bruce,ON,6000
Bob,BC,2000
Mike,BC,122
Bruce,BC,367
Craig,ON,3400
Craig,BC,1200
];

Output:

Load
Name,
max(Sales) as MaxSale
Resident Input Group By Name;

Drop Table Input;