identify last click with buy

[#1]

Hello Qlikview World,

I'm a qlikview newbie and maybe you can help!

How I can find in a list of visits from my website the last click with a buy???

For example:

VisitID	V_timestamp	OrderID	O_timestamp
A	01.03.2012 09:00:00
B	02.03.2012 21:12:12	X1	02.03.2012 21:12:12
C	03.03.2012 17:12:29
D	03.03.2012 22:17:09	X2	03.03.2012 22:17:09
E	04.03.2012 09:12:01
F	05.03.2012 21:08:54	X3	05.03.2012 21:08:54
G	06.03.2012 21:17:18

What is the function "firstsortedvalue" is doing? How I can realize the numeration for the BuyOrder??

My result table should look like this by adding a column with a buying order.

VisitID	V_timestamp	OrderID	O_timestamp	BuyOrder
B	02.03.2012 21:12:12	X1	02.03.2012 21:12:12	1
D	03.03.2012 22:17:09	X2	03.03.2012 22:17:09	2
F	05.03.2012 21:08:54	X3	05.03.2012 21:08:54	3

After this I just what identify the last buy.
Maybe here I can use the Set Analysis like: OrderSorting = {$(=Max(OrderSorting))}

ANY ADVICE WOULD BE HELPFUL!!!

THX
Frank

[qliksus]

HI,

AS per your data model V_Timestamp is the visit time i guess so u can use something like

load

visitid,

v_timestamp,

Orderid,

O_timestamp,

Rowno() as Buyorder ;

load ,

visitid,

v_timestamp,

Orderid,

O_timestamp

from TAblea

where Orderid is not null

;

after this u can use this Buyorder column in the chart level

[whiteline]

Firstsortedvalue is an aggergation function.

You can use O_timestamp as a sort-weight.

But before, it should be transformed from string to number:

TimeStamp#(O_timestamp, 'DD.MM.YYYY hh:mm:ss')

You can use the above transform in expression or in your load script.

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, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.