topic Re: Re: How to join with timestamp in QlikView

How to join with timestamp

Mon, 10 Nov 2014 10:52:48 GMT

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?

Re: How to join with timestamp

marcus_sommer — Mon, 10 Nov 2014 11:35:14 GMT

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

Re: How to join with timestamp

MarcoWedel — Tue, 11 Nov 2014 00:06:33 GMT

Hi,

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

FOR i = 1 TO 2

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

Re: Re: How to join with timestamp

Tue, 11 Nov 2014 06:02:14 GMT

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;

Re: Re: How to join with timestamp

Tue, 11 Nov 2014 06:04:07 GMT

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.

Re: Re: How to join with timestamp

marcus_sommer — Tue, 11 Nov 2014 08:22:01 GMT

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

Re: How to join with timestamp

Anonymous — Tue, 31 Oct 2017 03:09:06 GMT

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