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;

[agigliotti]

your code looks good.

what's happening ?

[alextomlins]

I don't know how better to explain it other than it isn't sorting. The very first customer ID has datetime that are not sorting.

The customerID is sorted but that could be just because that's how it is contained in the QVD. i am just trying to order the customers events so i can say, ' Customer 1's first action was, second action was and so on'

[agigliotti]

did you try sorting the table object as you need ?

Also let's try to remove the second column from table and see what you get.

[alextomlins]

I've tried it on the QVD load, which i believe isn't possible to do. I have also tried it on the first load - i actually thought i could do it there. I only loaded in another table to try and solve the problem lol

The original date is DD/MM/YYYY hh:mm:ss.

I can't think what i'm doing wrong here but it must be something !

[marcus_sommer]

You need to differ between sorting a table within a load (not possible by external sources else only by resident-loads) and the sorting within an object within the UI because a table-sorting isn't a sorting of the included fields and their fieldvalues and has nothing to do with a UI sorting. To sort the fieldvalues you could do something like this:

Start:

LOAD

num([LicenceeID]) as CustomerID_temp,

LicenseStatusDescription as LicenseStatusDescription2,

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

From RawData;

ThisTable:

LOAD

CustomerID_temp as CustomerID,

LicenseStatusDescription2,

EventTime_temp as EventTime

Resident Start

Order by CustomerID_temp, EventTime_temp Asc;

drop tables Start;

In View you could handle such numeric sorting very easily within the object - by Sense I'm not sure which possibilities especially sorting through expressions are available. If you could try: avg(EventTime).

- Marcus

[alextomlins]

Hi Marcus,

Thank you for your response. I have used your script and it has still not ordered it in the way i expect.

I am using data manager to look at the tables and see if the sort is correct. I am not using the UI to look at sort order because i understand that is affected by the UI sort configurations.

I am beginning to think that the data manager sorts by itself and just looks at content.

Is there anyway of seeing the actual real sort of my table values so that I know that my script is doing the right things ?

Thanks,

Alex

[marcus_sommer · Accepted solution]

I don't know if the datamanager has any capabilities to sort the tables or if it's a 1:1 preview of the table or just a tablebox like in the UI (and created from the entire datamodel). Just for checking you could add a recno() and a rowno() within the sorted load - and then you could see which record from the source is now whih record in the target.

- Marcus

[swuehl]

Add a Recno() or Rowno() created counter to your script to see the actual order of internal records.

LOAD

     Rowno() as RowID,

     ....

Are you 100% sure you are not unintentionally auto-concatenating the two tables and only looking at the start of the first (unsorted) table?

[alextomlins]

Hi Marcus - You are absolutely correct, The data manager does not display the raw order of the data. It worked the whole time!

I am surprised that i am unable to check results other than in UI where it is interfered with somewhat by the UI sorting. Eitherway I now have a way to check order thanks.

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