Select only first of several events

[#1]

Hi,

I have several timestamped events but want the applikation only to use the first of the events. How do I do?

ex

InvoiceNo 10, amount 1, datetime 1

Invoice No 10, amount 2, datetime 2

Invoice No 10, amount 3, datetime 3

I only want to use the amount and event connected to datetime 1.

[pat_agen]

hi,

have you looked at the firstsortedvalue() function?

help says:

firstsortedvalue ( [ distinct ] expression [, sort-weight [, n ]] )

returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

in your case you could try the following:

load InvoiceNumber,

firstsortedvalue(amount,datetime) as FirstAmount

from datasource

group by InvoiceNumber;

does this do the job?

[#3]

See if you can elaborate with some example.

[Anonymous]

Hi BlackRockS,

Here's an example related to your data using the peek() function to set a flag for when the InvoiceNo changes, in order to mark the first event.

Please see attached example.

[Miguel_Angel_Baeyens]

Hello,

You can use the following code, using your own fields:

Sum(If(Aggr(NODISTINCT Min(datetime), InvoiceNo) = datetime, amount))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

hi,

I did not get the firstsortedvalue(amount,datetime) as FirstAmount to work since the FIrstAmount couldn't be found. tried it on test data as an expression and it worked fine.

Tried the

Sum(If(Aggr(NODISTINCT Min(datetime), InvoiceNo) = datetime, amount))

and it worked fine.

I still have a problem though with both solutions. Sometimes there are invoices with several amounts on the same date and then I would like to get the largest number of the both. How do I add that to any of the two solutions above?