Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bibopipo
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 ?

Help please, I'm really lost

Thanks in advance

1 Solution

Accepted Solutions
henrikmatz
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:

mapping load

customer,

max(value)

from yourtable

group by customer

FInalTable:

load

*,

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

from yourtable;

/henrik

4 Replies
henrikmatz
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:

mapping load

customer,

max(value)

from yourtable

group by customer

FInalTable:

load

*,

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

from yourtable;

/henrik

MVP & Luminary
MVP & Luminary

Re: How tom mark max values only

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.

bibopipo
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 ?

Thanks in advance

stefan

henrikmatz
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

Community Browser