How tom mark max values only

[Anonymous]

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 ?

Help please, I'm really lost

Thanks in advance

[Anonymous · Accepted solution]

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:

mapping load

customer,

max(value)

from yourtable

group by customer

FInalTable:

load

*,

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

from yourtable;

/henrik

[jagan]

Hi,

Try this script below

Temp:   

LOAD * INLINE [

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:

LOAD

    *,

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

Resident Temp

ORDER BY Customer, Value Desc;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

[Anonymous]

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 ?

Thanks in advance

stefan

[Anonymous]

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