2 Replies Latest reply: Oct 10, 2013 6:02 AM by Quy Thanh Lê RSS

    Sort by timestamp and comparison

      Hi,

       

      I have some trouble to sort a table by date (timestamp) and compare two columns in a script

      I have a table like this :

      IdentifierDateTextATextB
      101/01/13 12:00:00BC
      101/01/13 13:00:00CA
      101/01/13 11:00:00AB
      201/01/13 12:00:00AABB
      201/01/13 14:00:00BBAA

       

      I want to show the data like this :

      IdentifierText
      1A-B-C-A
      2AA-BB-AA
      3X-Y-X

       

      To do so, I must load data from a database, sort by date (with timestamp !) and concatenate TextA & TextB in the good order (date) and without double, group by Identifier

       

      I have tried to :

      Class_sort:

      LOAD

           Identifier,

           TextA,

           TextB,

           Date,

           Resident Table_Temp_From_database ORDER BY Date ASC;

      But the "ORDER BY" doesn't seem to work

       

      Then I try to concatenate with concat

      LOAD

           Concat(TextA, '-' ),

           Resident Class_sort GROUP BY Identifier;

       

      But as the table is not sort by date the concatenation is not good..

      Moreover, my problem is that I must compare if previous.TextB = current.TextA before the concatenation to have (A-B-C-A) and not (A-B-B-C-C-A)... But as my table is not sort by date I don't know how to do so...

       

      Does anybody can help me with this ?

      Thank you in advance