# Unable to join data in TICK script
**URL:** https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342
**Category:** Kapacitor
**Tags:** kapacitor
**Created:** [January 29, 2019, 10:15pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342 "2019-01-29T22:15:43Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@BhanuKiran](https://community.influxdata.com/u/BhanuKiran)
#### Post date: [January 29, 2019, 10:15pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/1 "2019-01-29T22:15:43Z")
I have below TICK script
```
// Parameters
var period = 10s
var every = 10s
var tolerance = 10d
// Dataframe
// check - http://192.168.0.126:9092/kapacitor/v1/tasks/voc_stats/step1
// check - http://192.168.0.5:9092/kapacitor/v1/tasks/voc_stats/step1
var hourlyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(1h)
.every(every)
|httpOut('step1')
var weeklyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(7d)
.every(every)
|httpOut('step2')
var weeklyMinimum = batch
|query('''SELECT min(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(7d)
.every(every)
|httpOut('step3')
var data = hourlyAverage
|join(weeklyAverage, weeklyMinimum)
.as('hourlyAverage', 'weeklyAverage', 'weeklyMinimum')
.tolerance(8d)
|httpOut('step4')
```
I can observer the http ouput from step1,2&3 but step 4 is always outputting null series.
httpOut looks like this
step1 :
`{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-29T21:10:26.908059043Z",0.784185539493217]]}]}`
Step2:
`{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-22T22:10:46.908007082Z",3.631224120920862]]}]}`
Step3:
`{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-28T00:21:15Z",0.1752190237792004]]}]}`
step4:
`{"series":[{"name":"cpu"}]}`
---
### Author:  [@BhanuKiran](https://community.influxdata.com/u/BhanuKiran)
#### Post date: [February 1, 2019, 3:15pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/2 "2019-02-01T15:15:13Z")
@rawkode, @katy, @Anaisdg@philb Any comments on this ?
---
### Author:  [@rawkode](https://community.influxdata.com/u/rawkode)
#### Post date: [February 1, 2019, 9:42pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/3 "2019-02-01T21:42:38Z")
Should step 4 be:
```auto
var hourlyAverage | batch
|join(weeklyAverage, weeklyMinimum)
.as('hourlyAverage', 'weeklyAverage', 'weeklyMinimum')
.tolerance(8d)
|httpOut('step4')
```
---
### Author:  [@BhanuKiran](https://community.influxdata.com/u/BhanuKiran)
#### Post date: [February 1, 2019, 9:45pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/4 "2019-02-01T21:45:55Z")
Hi @rawkode,
I just copy paste the script it says
`invalid TICKscript: parser: unexpected | line 36 char 19 in "lyAverage | batch". expected: "identifier"`
line 36 is`hourlyAverage | batch`
---
### Author:  [@rawkode](https://community.influxdata.com/u/rawkode)
#### Post date: [February 1, 2019, 9:47pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/5 "2019-02-01T21:47:35Z")
You’re right, that was sloppy copy-pasting on my part.
```auto
var hourlyAverage = join(weeklyAverage, weeklyMinimum)
.as('hourlyAverage', 'weeklyAverage', 'weeklyMinimum')
.tolerance(8d)
|httpOut('step4')
```
---
### Author:  [@rawkode](https://community.influxdata.com/u/rawkode)
#### Post date: [February 1, 2019, 9:49pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/6 "2019-02-01T21:49:21Z")
Wait, are you using TICK or Flux?
---
### Author:  [@BhanuKiran](https://community.influxdata.com/u/BhanuKiran)
#### Post date: [February 1, 2019, 9:50pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/7 "2019-02-01T21:50:07Z")
Hi @rawkode
I am using TICK script editor from chronograf GUI.
Getting this error`invalid TICKscript: line 28 char 6: cannot get properties of non pointer value`
---
### Author:  [@BhanuKiran](https://community.influxdata.com/u/BhanuKiran)
#### Post date: [February 4, 2019, 7:55pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/8 "2019-02-04T19:55:10Z")
@rawkode Any possible answer for this ? or Do need any extra information on this ? I am running docker from influxdb sandbox. Tried both`./sandbox up` and`./sandbox up - nightly` as well
I have tried almost the same script as this today and it seems not working as well
```
var every = 15s
var custom_period = 3h
// Dataframe
// check - http://192.168.0.123:9092/kapacitor/v1/tasks/Rule-1-cpu/step1
var hourlyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(1h)
.every(every)
.fill('none')
.align()
|httpOut('step1')
// Custom hours ago average
var customAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(1h)
.every(every)
.fill('none')
.align()
// Query data from custom time ago
.offset(custom_period)
|shift(custom_period)
|httpOut('step2')
var weeklyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(7d)
.every(every)
.fill('none')
.align()
|httpOut('step3')
var weeklyMinimum = batch
|query('''SELECT min(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(7d)
.every(every)
.fill('none')
.align()
|httpOut('step4')
var data = hourlyAverage
|join(customAverage, weeklyAverage, weeklyMinimum)
.as('hourlyAverage', 'customAverage', 'weeklyAverage', 'weeklyMinimum')
.tolerance(2w)
|httpOut('step5')
```
Here i have similar outputs
step 1:
`{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-02-04T18:51:30Z",5.510724480730352]]}]}`
step 2:
`{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-02-04T18:51:30Z",3.297008825595237]]}]}`
step 3:
`{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-01-28T19:51:45Z",4.021326806086728]]}]}`
step 4:
`{"series":[{"name":"cpu","columns":["time","usage_user"],"values":[["2019-02-04T07:04:30Z",0.17513134851138412]]}]}`
step5:
`{"series":[{"name":"cpu"}]}`
I have tried plenty of permutation and combination but nothing giving me the result, i really appreciate if can provide me a way around.
Thanks
---
### Author:  [@rawkode](https://community.influxdata.com/u/rawkode)
#### Post date: [February 5, 2019, 6:50pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/9 "2019-02-05T18:50:41Z")
Hi @BhanuKiran,
Let me run some tests and I’ll get back to you as soon as I can.
Sorry for the delay 👍
---
### Author:  [@rawkode](https://community.influxdata.com/u/rawkode)
#### Post date: [February 6, 2019, 7:03pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/10 "2019-02-06T19:03:56Z")
Hi @BhanuKiran,
This appears to be a way the join handles the batched points, but this can be fixed by utilising `last()`.
```auto
var period = 10s
var every = 10s
var tolerance = 10d
var hourlyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(1h)
.every(every)
|last('usage_user')
.as('usage_user')
var weeklyAverage = batch
|query('''SELECT mean(usage_user) AS usage_user FROM "telegraf"."autogen"."cpu" WHERE cpu = 'cpu-total' ''')
.period(7d)
.every(every)
|last('usage_user')
.as('usage_user')
hourlyAverage
|join(weeklyAverage)
.as('hourlyAverage', 'weeklyAverage')
.tolerance(1s)
```
Thanks for your patience, hope this helps!
---
### Author:  [@BhanuKiran](https://community.influxdata.com/u/BhanuKiran)
#### Post date: [February 6, 2019, 8:08pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/11 "2019-02-06T20:08:13Z")
Thanks @rawkode . 🥰
It’s an amazing work around. I have gone through many post but none of them explained this trick. I am also happy that this also solved my another issue of time stamping. In below example, although join node is succeeded but it has an issue with the time stamp.
```
// Parameters
var every = 10s
// Dataframe
// check - http://ip/kapacitor/v1/tasks/test/step1
// Past one week average
var weeklyAverage = batch
|query('''SELECT mean(voc) AS voc_0015BC0036000018 FROM "sensordata"."autogen"."voc" WHERE eui = '0015BC0036000018' ''')
.period(7d)
.every(every)
.groupBy('eui')
.align()
|httpOut('step1')
// Past one week minimum
var weeklyMinimum = batch
|query('''SELECT mean(voc) AS voc_0015BC0036000018 FROM "sensordata"."autogen"."voc" WHERE eui = '0015BC0036000018' ''')
.period(7d)
.every(every)
.groupBy('eui')
.align()
|httpOut('step2')
var data = weeklyAverage
|join(weeklyMinimum)
.as('weeklyAverage', 'weeklyMinimum')
.tolerance(2w)
|httpOut('step3')
data
|influxDBOut()
.database('telegraf')
.retentionPolicy('autogen')
.measurement('cpu_stats')
```
If see the http output
step#1
`{"series":[{"name":"voc","tags":{"eui":"0015BC0036000018"},"columns":["time","voc_0015BC0036000018"],"values":[["2019-01-30T19:54:10Z",107.27860696517413]]}]}`
step#2
`{"series":[{"name":"voc","tags":{"eui":"0015BC0036000018"},"columns":["time","voc_0015BC0036000018"],"values":[["2019-01-30T19:54:10Z",107.27860696517413]]}]}`
step#3
`{"series":[{"name":"voc","tags":{"eui":"0015BC0036000018"},"columns":["time","weeklyAverage.voc_0015BC0036000018","weeklyMinimum.voc_0015BC0036000018"],"values":[["2019-01-28T00:00:00Z",107.27860696517413,107.27860696517413]]}]}`
I observe the time stamp in step 3 its always “2019-01-28T00:00:00Z” in every single execution. Effectively Influxdb out node is overwriting the same data point every single time of its execution.
But now i think its will form time series, rather than over writing the same data point again and again.
Thanks, really appreciated.
---
### Author:  [@nated099](https://community.influxdata.com/u/nated099)
#### Post date: [October 17, 2019, 7:38pm UTC](https://community.influxdata.com/t/unable-to-join-data-in-tick-script/8342/12 "2019-10-17T19:38:36Z")
I’ve been searching for this solution for weeks. This should really be put on influxdata joinnode page as a sidenote or something. Thank you!