topic Re: Rank by Account and Order Time in QlikView

Rank by Account and Order Time

Wed, 18 Mar 2015 14:50:24 GMT

My data model has three fields I need to work with here: (I can't attach a sample QVW due to confidentiality. This is over-simplified.)

AccountNumber	Item	OrderDateTime
A123	Widget1	1/1/2015, 09:00
A123	Widget2	1/4/2015, 10:05
A123	Widget1	1/4/2015, 08:12
C456	Widget3	2/5/2015, 15:04
C456	Widget3	2/15/2015, 15:01

I need to rank them by AccountNumber and OrderDateTime, so it looks like this:

AccountNumber	Item	OrderDateTime	Rank
A123	Widget1	1/1/2015, 09:00	1
A123	Widget2	1/4/2015, 10:05	3
A123	Widget1	1/4/2015, 08:12	2
C456	Widget3	2/15/2015, 15:04	2
C456	Widget3	2/15/2015, 15:01	1

Questions:

1. What is the proper syntax for my chart expression?

2. Can I do this in a straight table, or does this need to be in a pivot table?

3. I would really like to have "Rank" as a field, rather than a chart expression. That way, I would have a sheet object to select only the items with a "Rank" = 1. How can I add the ranking to my script?

Thanks,

Sam.

Re: Rank by Account and Order Time

MK_QSL — Wed, 18 Mar 2015 15:30:09 GMT

Test:

Load

AccountNumber,

Item,

TimeStamp(TimeStamp#(OrderDateTime,'M/D/YYYY hh:mm')) as OrderDateTime

Inline

[

AccountNumber, Item, OrderDateTime

A123, Widget1, 1/1/2015 09:00

A123, Widget2, 1/4/2015 10:05

A123, Widget1, 1/4/2015 08:12

C456, Widget3, 2/15/2015 15:04

C456, Widget3, 2/15/2015 15:01

];

Final:

Load

AutoNumber(OrderDateTime, AccountNumber) as Rank

Resident Test

Order By AccountNumber, OrderDateTime;

Drop Table Test;

Re: Rank by Account and Order Time

Wed, 18 Mar 2015 15:49:45 GMT

Thanks, Manish. My data set is very large, and doesn't lend itself to using an inline table.

My table is named "OeOrders". Can I place this script immediately after OeOrders loads, and replace the inline table portion of the script with "Resident OeOders"? Or can you recommend another option?

Re: Rank by Account and Order Time

MK_QSL — Wed, 18 Mar 2015 15:57:21 GMT

Inline Table I have given for sample purpose.

You can obviously use the Resident Load for creating the AutoNumber Rank and later drop the main table