topic Re: Set Analysis solution in QlikView

Set Analysis solution

Anonymous — Wed, 01 Oct 2014 08:04:14 GMT

Hi Guys,

I need a syntax for the following problem

I have a table with dimensions; Vendor,Item,Time (po date) and the expression is unit price. What I need is the lastest Po date of that item with the corresponding unit price. With that price I will calculate with other fields. So when I troggle on Vendor or Item and time, I always need the latest PO with unit price from the database.

VENDOR NAME	ITEM	PO LINE CREATION DATE	UNIT PRICE
AxFloX	005870	14-1-2014	37,62
AxFloX	005870	17-6-2014	40,68

I think this can be achieved with the correct set analysis in the presentation layer but perhaps a solution in the script is also no problem...

Thanks in advance

Best regards

Robert

Re: Set Analysis solution

ashfaq_haseeb — Wed, 01 Oct 2014 08:20:13 GMT

Try first sorted value

Like below

FirstSortedValue(UNIT PRICE, -PO LINE CREATION DATE)

Regards

ASHFAQ

Re: Set Analysis solution

alexandros17 — Wed, 01 Oct 2014 08:29:01 GMT

I should solve on script side, simply doing this:

If your table name is myTable then

Load * resident myTable;

left join

Load max([creation date]) as [creation date], '1' as flag resident myTable group by [Vendor Name], Item;

Now you have a flag field showing max (per Vendor and Item), use this field in your expression with set analysis and it will work

Let me Know