# How to merge 2 historic tag datasets?

**URL:** <https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [May 24, 2023, 11:45am UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116 "2023-05-24T11:45:02Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 11:45am UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/1 "2023-05-24T11:45:02Z")

</div>

hi All  
I am trying to display in the table historic data for selected tags.  
The data for each selected tag in the tag browse tree is retrieved on the double click event using system.tag.queryTagHistory for the last 1 minute.  
Then when another tag is selected i was trying to merge the datasets using  
system.dataset.appendDataset but instead of having a new column in the data i have data appended to the existing columns.  
Also data format is a very long number instead of date ☹  
Any pointers how to solve it?

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 1:44pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/2 "2023-05-24T13:44:25Z")

</div>

2nd attempt now:  
instead of

> system.dataset.appendDataset

i am trying to add column using

> system.dataset.addColumn(dataset, [colIndex], col, colName, colType)  
> my big problem is what value to pass as colType ?  
> the values i am added to the new column are coming from the timestamp from the dataset retrieved using

> system.tag.queryTagHistory

when i printed the type using

> type(dataset.getValueAt(0,0))  
> i got reply as

> class java.sql.Timestamp

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 24, 2023, 1:54pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/3 "2023-05-24T13:54:17Z")

</div>

> [@hewking32](#):
>
> the values i am added to the new column are coming from the timestamp from the dataset retrieved using

Why are you trying to add a timestamp column?

If you are trying to combine the two historical datasets, then you would want to insure that the timestamps matched for all rows. If there is interpolation, or the tags are collected at different rates, that is going to be non-trivial.

Honestly your best bet is to just rerun the query with the additional tag.

> [@hewking32](#):
>
> Also data format is a very long number instead of date

The historian stores timestamps in milliseconds from epoch. It is a timestamp it just isn't in the format you would traditionally expect. You can use `system.date.fromMillis()` to convert if needed.

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 2:12pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/4 "2023-05-24T14:12:22Z")

</div>

> If you are trying to combine the two historical datasets, then you would want to insure that the timestamps matched for all rows

Yes that is a good point that timestamps will not match to the millisecond but they match close enough for comparison. When adding new dataset i am actually adding 2 columns with time and actual value which is float. I tried to use system.date.fromMillis() to convert from milliseconds to "Date".  
However when using "Date" as column typei get an error still  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/6/565bd75180702c168bddf8342877146fbf5203ca.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/d/5d27f6e66f18521031395a75894f3a065fd74192.png)

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 24, 2023, 2:14pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/5 "2023-05-24T14:14:10Z")

</div>

can you show your script?

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 2:16pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/6 "2023-05-24T14:16:45Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/e/3e331e76d0d9974a4b0299b391e8519ebb8f0eb6.png)  
i missed in my first screenshot column type which is the main troublemaker

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 24, 2023, 2:31pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/7 "2023-05-24T14:31:27Z")

</div>

When sharing code, don't use screenshots.  
Use the code formatting button:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/a/faee29224e24d09f9ad3092dbc28397a32aff844.png)

and paste your code in between the 2 lines of backticks, like so:  
```  
code  
```

you can even specify the language to add syntax highlighting:  
```python  
code  
```

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 24, 2023, 2:31pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/8 "2023-05-24T14:31:47Z")

</div>

Okay, first things first. Please don't post screen shots of code without also posting a copy of the script using the preformatted text option. It makes it really difficult for us to help you.

Second, I assume that this is just for testing purposes at this point, as the script doesn't currently do what you originally said you were attempting to do.

I think that I, at least, need a better understanding of what exactly you're attempting to accomplish.

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 2:39pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/9 "2023-05-24T14:39:37Z")

</div>

```auto
def runAction(self, event):
	from java.util import Date

	endTime = system.date.now()
	startTime = system.date.addMinutes(endTime, -1)
	dataSet1 = system.tag.queryTagHistory(paths=['[default]tag1'], startDate=startTime, endDate=endTime, returnSize=5, aggregationMode="Maximum", returnFormat='Wide')
	dataSet2 = system.tag.queryTagHistory(paths=['[default]tag2'], startDate=startTime, endDate=endTime, returnSize=5, aggregationMode="Maximum", returnFormat='Wide')
	
	columnData1 = []
	columnData2 = []	
	for i in range(dataSet2.getRowCount()):
		columnData1.append(system.date.fromMillis( dataSet2.getValueAt(i,0)) )	
		columnData2.append(dataSet2.getValueAt(i,1))		
	
	self.parent.parent.parent.getChild("Label").props.text = type(dataSet2.getValueAt(0,0))				
													
	newDataSet_addedTimestamp = system.dataset.addColumn(dataSet1,2,columnData1,"sin1_time",Date)	
	newDataSet_addedSin1 = system.dataset.addColumn(newDataSet_addedTimestamp,3,columnData2,"sin1_value",float)												
																																							
	self.parent.parent.getChild("Table").props.data = newDataSet_addedSin1

```

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 2:41pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/10 "2023-05-24T14:41:06Z")

</div>

i am trying to display in the table component tag history values from the selected tags in the tag tree.  
the code i pasted i test code to add column with the second selected tag which is hardcoded at the moment

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 24, 2023, 2:46pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/11 "2023-05-24T14:46:44Z")

</div>

Any reason you're not passing both tags to `queryTagHistory` ?

The doc says:

> List[String] paths - An array of Tag paths (strings) to query. Each Tag path specified will be a column in the result dataset.

Now, I know historian query functions returns are sometimes.... funky. But start with that.

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 2:50pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/12 "2023-05-24T14:50:08Z")

</div>

wow thanks for pointing this out - this will be my fallback solution but i am still pushing for the original add.column solution if it works 🙂  
the reason for that is that when using mobile device tag have to be selected add by one so its handy to keep merging the datasets.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [May 24, 2023, 2:53pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/13 "2023-05-24T14:53:24Z")

</div>

> [@hewking32](#):
>
> the reason for that is that when using mobile device tag have to be selected add by one so its handy to keep merging the datasets.

Honestly, while it is completly possible to merge the datasets, you're better off just using the list of selected tags and then querying the history for all tags again.

The script as it is right now will only ever query for `tag1` and `tag2`. There is nothing that is actually getting the selected values from the tag browse tree.

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [May 24, 2023, 2:54pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/14 "2023-05-24T14:54:41Z")

</div>

Store the tags in a list then query all of them at once. Don't make things more complicated than they actually are. Which, in programmer language, means "use the built-ins when they exist"

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 3:03pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/15 "2023-05-24T15:03:29Z")

</div>

> Honestly, while it is completly possible to merge the datasets, you're better off just using the list of selected tags and then querying the history for all tags again.

thats correct selection list needs to be added to the code  
yes i will use the list of tags which do works reliably as i tried just now  
thanks a lot for help

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 3:06pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/16 "2023-05-24T15:06:04Z")

</div>

@pascal.fragnoud thanks for help as well as @lrose

---

<div class="post-metadata">

**Author:** ![hewking32](https://avatars.discourse-cdn.com/v4/letter/h/b5ac83/32.png) [@hewking32](https://forum.inductiveautomation.com/u/hewking32)\
**Post date:** [May 24, 2023, 3:22pm UTC](https://forum.inductiveautomation.com/t/how-to-merge-2-historic-tag-datasets/74116/17 "2023-05-24T15:22:31Z")

</div>

@lrose you mentioned that system.date.fromMillis will convert the time in the historic dataset but how do i actually do it? i would prefer to avoid looping via whole dataset and calling this function.  
Can i somehow format the table itself?