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