5 Replies Latest reply: Nov 11, 2014 3:22 AM by Marcus Sommer RSS

    How to join with timestamp

    Mika Lindman

      Hi,

       

      I'm trying to calculate rolling averages, so I need the values from previous tables. For some reason my join doesn't work properly, and I can't figure out why.

       

      temp:
      LOAD * INLINE [
      Timestamp, Value
      '1.1.2014 01:00', 1.1
      '1.1.2014 02:00', 0.4
      '1.1.2014 03:00', 0.2
      '1.1.2014 04:00', 0.8
      '1.1.2014 05:00', 1.5
      '1.1.2014 06:00', 1.8
      '1.1.2014 07:00', 1.9
      '1.1.2014 08:00', 2.1];
      
      
      final:
      NOCONCATENATE LOAD
        timestamp#(Timestamp, 'dd.MM.yyyy HH:mm') AS Key.Timestamp,
        Value
      RESIDENT temp;
      
      
      FOR i = 1 TO 2
        t:
        NOCONCATENATE LOAD
        timestamp#(Key.Timestamp - Maketime($(i)), 'dd.MM.yyyy HH:mm') AS Key.Timestamp,
        Value AS Value_t_minus_$(i)
        RESIDENT final;
      
        JOIN (final)
        LOAD
        timestamp#(Key.Timestamp, 'dd.MM.yyyy HH:mm') AS Key.Timestamp,
        Value_t_minus_$(i)
        RESIDENT t;
      
        DROP TABLE t;
      NEXT i;
      
      
      DROP TABLE temp;
      
      
      
      
      
      
      
      
      

       

      I'm making sure my timestamps are the same format, so this shouldn't be the problem. Why isn't this working properly?