Why can't i use order by here? !

[alextomlins]

i am just trying to order the events by CustomerID and then Event type so per customer their events are chronological. I must be missing a lot here. It's very frustrating haha ! Is there a reason sorting seems to be impossible for me here.

Start:

LOAD

num([LicenceeID]) as CustomerID,

LicenseStatusDescription as LicenseStatusDescription2,

Date#(LoggedDateTimeUtc, 'DD/MM/YYYY hh:mm:ss') as EventTime

Resident LicensingLicenseHistory;

Noconcatenate

ThisTable:

LOAD

CustomerID,

LicenseStatusDescription2,

EventTime

Resident Start

Order by CustomerID, EventTime Asc;

[alextomlins]

The data manager does not display the raw order of the data. It worked the whole time! Thank you for your help. I've also asked Marcus this but ;

Do you know anyway that I can get an order number of sequential records per customer. So I can say - for customer 1, transaction 1, 2, 3, 4  customer 2 transaction 1,2. This is with the hope to aggregate all first transactions per customer?

Thanks,

Alex

[marcus_sommer]

You could create such counter with something like:

autonumber(CustomerID & '|' & OrderID)

- Marcus

[alextomlins]

Cool thankyou, i'll use autonumber. Is  OrderID the Rowno() alias?

[marcus_sommer]

No not really else an ID for the transaction which is often an OrderID or similar. In some cases the rowno() could be included to get the wanted counter but if you have for example multiple items on one order the rowno() usage might mislead you. If possible use the ID's which comes with your data and create own ones only if necessary.

- Marcus

[swuehl]

I think we need to use the autonumber() with two parameter to get the counter reset with new customer.

Hence, with your input table sorted in transaction order (though customers could be mixed), e.g.  sorted by transaction timestamp:

Autonumber(Recno(), CustomerID) as CustomerTransactionID

or if you reset by Customer and  OrderID:

Autonumber(Recno(), CustomerID&'-'&OrderID) as CustomerOrderTransactionID

Similar can be done using Peek() / Previous() in the script.

Counters in the Load