# New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Contributor II

## How tom mark max values only

Dear all,

I'm newer and I'm facing a problem too big for my experience. I've this scenario

Table1

Customer     Article     Line Value

A                 1             1       100

A                 1              2      200

A                 1             3        80

B                 2             1        40

B                 2             2        30

B                 2             3        10

C                 1             1        20

C                 1             2       100

I would insert a flag into the lines which have the maximum value for each combination of Customer,Article, line:

NewTable1

Customer     Article     Line Value  FlagMax

A                 1             1       100

A                 1              2      200     *

A                 1             3        80

B                 2             1        40       *

B                 2             2        30

B                 2             3        10

C                 1             1        20

C                 1             2       100     *

Is it possible ?

1 Solution

Accepted Solutions
Contributor II

## Re: How tom mark max values only

HI,

i would first creat a mapping table with customer and max(value) grouped by customer.

In the load of the table I would then use applymap function to lookup the max value. If the max value is equal to the value field I would set a flag.

it could be like this:

MAPmax:

customer,

max(value)

from yourtable

group by customer

FInalTable:

*,

If(applymap('MAPmax',customer)=value,1) as FlagMax

from yourtable;

/henrik

4 Replies
Contributor II

## Re: How tom mark max values only

HI,

i would first creat a mapping table with customer and max(value) grouped by customer.

In the load of the table I would then use applymap function to lookup the max value. If the max value is equal to the value field I would set a flag.

it could be like this:

MAPmax:

customer,

max(value)

from yourtable

group by customer

FInalTable:

*,

If(applymap('MAPmax',customer)=value,1) as FlagMax

from yourtable;

/henrik

MVP & Luminary

## Re: How tom mark max values only

Hi,

Try this script below

Temp:

Customer,     Article,     Line, Value

A ,                1,             1 ,      100

A,                 1 ,             2 ,     200

A,                 1 ,            3 ,       80

B ,                2 ,            1,        40

B,                2 ,            2,        30

B ,                2 ,            3 ,       10

C,                 1 ,            1,        20

C ,                1 ,            2 ,      100

];

Data:

*,

IF(Previous(Customer) <> Customer, '*') AS Flag

Resident Temp

ORDER BY Customer, Value Desc;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

Contributor II

## Re: How tom mark max values only

Thanks Henrik, if works but I didn't realized how.

Could you spent a bit of your time to explain how the mapping load works ?

stefan

Contributor II

## Re: How tom mark max values only

A mapping load is used to create a mapping table.

A mapping table is a table of always 2 fields and the table only exists during the reload procedure. It is used when you want to lookup a value.

It's used a lot when you load a fact table and want to lookup a surgate dimension key from a dimension table. In this case you create a mapping table with the unique business key and the corresponding surrogate key.

Mapping load is faster than the lookup function, but the lookup function is more flexible.

You should take a look in the help area, and search for mapping load and lookup. I believe there are some examples.

/Henrik