topic Re: Re: FirstSortedValue in QlikView

FirstSortedValue

MK_QSL — Fri, 10 Jan 2014 12:59:48 GMT

Dimension

PartNumber

Expression

FirstSortedValue(Price,-InvoiceDate)

1) Giving me '-' whenever same part number is sold more than once on the same date.

2) What if same part number is sold more than once with different price

My Idea is to get Minimum Price for each part number sold on the latest Date...

Re: FirstSortedValue

hic — Fri, 10 Jan 2014 13:24:31 GMT

Try

FirstSortedValue(Aggr(Min(Price),PartNumber,InvoiceDate),-InvoiceDate)

HIC

Re: Re: FirstSortedValue

MK_QSL — Fri, 10 Jan 2014 13:27:31 GMT

Hi Henric,

Its not working...

Please check enclosed file..

Re: Re: Re: FirstSortedValue

hic — Fri, 10 Jan 2014 14:01:43 GMT

You can do it using a cross-table and the Above() function.

LastPrice = If(IsNull(Min(Price)),Above(LastPrice),Min(Price))

Re: FirstSortedValue

MK_QSL — Fri, 10 Jan 2014 14:35:22 GMT

Dear Henric,

Thanks for your valuable time but this will not work for me...

I have around 80,000 part numbers...

The preferable solution should be as per below table.

PartNumber	MinPrice	MaxInvoiceDate
A	105	08/01/2014
B	110	04/01/2014
C	130	10/01/2014
D	110	07/01/2014

Re: FirstSortedValue

MK_QSL — Fri, 10 Jan 2014 16:52:44 GMT

Any reply on this?

Your help would be highly appreciated...

Re: FirstSortedValue

jerem1234 — Fri, 10 Jan 2014 16:57:01 GMT

This might not be the most elegant, but you could sort using the concat function. Try this out:

subfield(concat(aggr(min(Price), PartNumber, InvoiceDate), '|', -InvoiceDate), '|', 1)

Hope this helps!

Re: FirstSortedValue

MK_QSL — Fri, 10 Jan 2014 17:20:20 GMT

Tricky and working but still would love to get another way.

Re: FirstSortedValue

whiteline — Fri, 10 Jan 2014 19:21:48 GMT

Hi.

This will give you the result:

=FirstSortedValue(Aggr(Min(Price),PartNumber,InvoiceDate),-aggr(InvoiceDate, PartNumber, InvoiceDate))

Re: FirstSortedValue

Fri, 10 Jan 2014 19:32:52 GMT

Hi,

Here is my suggestion:

=Min(if(InvoiceDate=aggr(NODISTINCT Max(InvoiceDate), PartNumber), Price))

Re: Re: FirstSortedValue

rwunderlich — Fri, 10 Jan 2014 19:57:25 GMT

How about:

=FirstSortedValue(DISTINCT Aggr(Min(Price),PartNumber,InvoiceDate),-InvoiceDate)

-Rob

Re: FirstSortedValue

whiteline — Fri, 10 Jan 2014 20:21:07 GMT

It works, but I don't understand why

Re: FirstSortedValue

MK_QSL — Fri, 10 Jan 2014 21:43:43 GMT

So after a very hard and tired day, I got some useful solutions.

Jerem

SubField(CONCAT(Aggr(MIN(Price),PartNumber,InvoiceDate),'|',-InvoiceDate),'|',1)

B Aydin

Min(If(InvoiceDate = Aggr(NODISTINCT Max(InvoiceDate), PartNumber), Price))

Would appreciate if you explain to understand the use of NODISTINCT in this expression...

Rob

FirstSortedValue(DISTINCT Aggr(Min(Price),PartNumber,InvoiceDate),-InvoiceDate)

Hi Rob, What is the reason of getting syntex error (red lines) in expression?

Whiteline

FirstSortedValue(Aggr(Min(Price),PartNumber,InvoiceDate),-Aggr(InvoiceDate, PartNumber, InvoiceDate))

Can anyone try to explain how above expression is working? Want to understand the magic behind two InvoiceDate in single Aggr.

Thanks all for your support.

Re: FirstSortedValue

rwunderlich — Fri, 10 Jan 2014 23:25:29 GMT

"... Hi Rob, What is the reason of getting syntax error (red lines) in expression?"

DISTINCT appeared in the doc some time back (I think an early QV10 SR) but the syntax checker was never updated. (or may have worked for a while -- can't recall). The red squiggle is probably why most people don't know about DISTINCT in FirstSortedValue . Welcome to the club.

-Rob

Re: FirstSortedValue

MK_QSL — Sat, 11 Jan 2014 09:00:42 GMT

Thanks ROB....