14 Replies Latest reply: Sep 21, 2018 5:09 AM by Stefan Wühl RSS

    Why can't i use order by here? !

    Alex Tomlins

      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;

        • Re: Why can't i use order by here? !
          Andrea Gigliotti

          your code looks good.

          what's happening ?

            • Re: Why can't i use order by here? !
              Alex Tomlins

              Capture.PNGI 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'

                • Re: Why can't i use order by here? !
                  Andrea Gigliotti

                  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.

                    • Re: Why can't i use order by here? !
                      Alex Tomlins

                      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 !

                        • Re: Why can't i use order by here? !
                          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

                            • Re: Why can't i use order by here? !
                              Alex Tomlins

                              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

                                • Re: Why can't i use order by here? !
                                  Marcus Sommer

                                  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

                                  • Re: Why can't i use order by here? !
                                    Stefan Wühl

                                    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?

                                      • Re: Why can't i use order by here? !
                                        Alex Tomlins

                                        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