Help using Max

[#1]

I'm new in QLikView. I have the following problem.

I have one table like this:

Item, Price, Currency

A, 2, USD, ...

A, 3, EUR, ...

B, 2, EUR, ...

B, 4, EEK, ...

B, 4, USD....

I would to create a new table like this

Item, MaxPrice, Currency

A, 3, EUR, ...

B, 4, EEK, ... (EEK or USD)

...

(MaxPrice is simply the Max value of Price, and the Currency is the currency related to the MaxPrice)

Which is the best way to do that?

Many thanks

[jonathandienst]

Hi

Create a straight table or pivot table with Item & Currency as dimensions and Max(Price) as the expression

Regards

Jonathan

[#3]

Hi Jonathan,

I tried to do the pivot, but... if the Item A has multiple Prices, with different currencies , the result is the following:

A, USD, 2, ..

A, EUR, 3

...

I would have

A, EUR, 3

Many Thanks

Martina

[jonathandienst]

Hi

So do you mean that you want the highest price for the item, and the currency for the highest price?

Regards

Jonathan

[#5]

Yes, exactly. I need to have a table with the highest price for the item, and the currency for the highest price.

Many Thanks

Martina

[Anonymous · Accepted solution]

Hi Martina,

You can do it in script level, the scripting code is as follow:

price:

LOAD * Inline [

Item, Price, Currency

A, 3, EUR

A, 2, USD

B, 2, EUR

B, 4, EEK

B, 4, USD

];

max:

LOAD

LastValue(Currency),

Item,

MaxString(Price) as [Max Price]

Resident price

Group By Item Order By Price;

DROP table price;

Hope it helps

Tiong

[#7]

Yes, it works!!!

Many Thanks

Martina