Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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?

Tags (3)
6 Replies

Re: How to join with timestamp

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

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

Re: Re: How to join with timestamp

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

Re: Re: How to join with timestamp

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

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

adjrockadjrock
New Contributor II

Re: How to join with timestamp

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

Community Browser