How to join with timestamp

[#1]

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?

[marcus_sommer]

I'm not sure if your approach could work. I think you need rather a logic with peek() to check the previous row. Have a look on these example for some hints:

Re: Re: cummulating previous interval days per yearmonth

Using Peek to get a Cummuliative Number

- Marcus

[MarcoWedel]

Hi,

maybe this works better, because you don't have to interpret timestamps that are already numeric?

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

Key.Timestamp,

  Value_t_minus_$(i)

  RESIDENT t;

  DROP TABLE t;

NEXT i;

hope this helps

regards

Marco

[#4]

Still can't get it to join properly. Here's the current version:

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, 'd.M.yyyy HH:mm') AS Key.Timestamp,
  Value
RESIDENT temp;


FOR i = 1 TO 2
  t:
  NOCONCATENATE LOAD
  timestamp(Key.Timestamp - Maketime(1 * $(i)), 'd.M.yyyy HH:mm') AS Key.Timestamp,
  Value AS Value_t_minus_$(i)
  RESIDENT final;

  JOIN (final)
  LOAD
  Key.Timestamp,
  Value_t_minus_$(i)
  RESIDENT t;

  DROP TABLE t;
NEXT i;


DROP TABLE temp;

[#5]

I ended up using peek() -method, but I think self-joining with loop could give more flexibility in some situations. Also the join not working is bugging me.

[marcus_sommer]

Maybe the reason for a not working joining is the timestamp - 1 or 2 hours. This would be produce a number with many decimals which would be rounded more or less correctly - see here: http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors

I think you should try it with something like round(num()). Maybe you creates an additionally table (with qualify) to look what your timestamp - 1 hour exactly returns.

- Marcus

[Anonymous]

Hi,

Was this resolved? I believe you were not able to join because Qlik store decimal value differently so 1.14 doesn't necessarily equal 1.14