Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to join with timestamp

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?

6 Replies
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

Not applicable
Author

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;

Not applicable
Author

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
Not applicable
Author

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