topic Re: Help using Max in QlikView

Help using Max

Fri, 11 May 2012 13:21:41 GMT

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

Re: Help using Max

jonathandienst — Fri, 11 May 2012 13:31:32 GMT

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

Regards

Jonathan

Help using Max

Fri, 11 May 2012 13:44:14 GMT

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

Re: Help using Max

jonathandienst — Sat, 12 May 2012 13:35:06 GMT

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

Regards

Jonathan

Re: Help using Max

Mon, 14 May 2012 06:45:55 GMT

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

Re: Help using Max

Anonymous — Mon, 14 May 2012 07:32:34 GMT

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

Re: Help using Max

Mon, 14 May 2012 08:51:38 GMT

Yes, it works!!!

Many Thanks

Martina