Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rank by Account and Order Time

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.)

AccountNumberItemOrderDateTime
A123Widget11/1/2015, 09:00
A123Widget21/4/2015, 10:05
A123Widget11/4/2015, 08:12
C456Widget32/5/2015, 15:04
C456Widget32/15/2015, 15:01

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

AccountNumberItemOrderDateTimeRank
A123Widget11/1/2015, 09:001
A123Widget21/4/2015, 10:053
A123Widget11/4/2015, 08:122
C456Widget32/15/2015, 15:042
C456Widget32/15/2015, 15:011

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.

3 Replies
MK_QSL
MVP
MVP

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;

Not applicable
Author

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?

MK_QSL
MVP
MVP

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