topic max in QlikView

max

Wed, 12 Nov 2014 22:15:46 GMT

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

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

Re: max

maxgro — Wed, 12 Nov 2014 22:17:15 GMT

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

Re: max

Wed, 12 Nov 2014 22:24:24 GMT

for me both istext & isnum returning 0

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

Re: max

Wed, 12 Nov 2014 22:27:02 GMT

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.

Table:

Load

Name,

Province,

Sales

from temp.qvd

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

Re: max

maxgro — Wed, 12 Nov 2014 22:31:04 GMT

I supposed in a chart, like this

Re: max

Wed, 12 Nov 2014 22:33:42 GMT

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

Re: max

maxgro — Wed, 12 Nov 2014 22:34:33 GMT

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

];

noconcatenate

load

Name, Province, Sales

Resident x

where peek(Name) <> Name

order by Name, Sales desc;

drop table x;

Re: max

Thu, 27 Nov 2014 07:51:42 GMT

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

Re: max

tresB — Thu, 27 Nov 2014 08:12:03 GMT

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;