topic Re: How tom mark max values only in QlikView

How tom mark max values only

Anonymous — Thu, 04 Oct 2012 20:51:47 GMT

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

Re: How tom mark max values only

Anonymous — Fri, 05 Oct 2012 04:33:07 GMT

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

Re: How tom mark max values only

jagan — Fri, 05 Oct 2012 11:24:52 GMT

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.

Re: How tom mark max values only

Anonymous — Fri, 12 Oct 2012 20:30:59 GMT

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

Re: How tom mark max values only

Anonymous — Fri, 12 Oct 2012 20:44:00 GMT

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