Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.