Forum Discussion
Question about pivoting timeseries
I am trying to do the following.
I have a database that have a timeserie like this:
| date | tags | value |
| 01/03/2020 | tag1 | 1 |
| 02/03/2020 | tag1 | 2 |
| 03/03/2020 | tag1 | 3 |
| 01/03/2020 | tag2 | 3.4 |
| 02/03/2020 | tag2 | 1.2 |
| 03/03/2020 | tag2 | 3.5 |
When I performa a pivot operation in this table I get this
| tag1 | tag2 | |
| 01/03/2020 | 1 | |
| 01/03/2020 | 3.4 | |
| 02/03/2020 | 2 | |
| 02/03/2020 | 1.2 | |
| 03/03/2020 | 3 | |
| 03/03/2020 | 3.5 |
Is there any way to make the time combine the time column in a way that I have something like this
| tag1 | tag2 | |
| 01/03/2020 | 1 | 3.4 |
| 02/03/2020 | 2 | 1.2 |
| 03/03/2020 | 3 | 3.5 |
I want this because I want to perform some calculation using the values in the same timestamps.
Regards,
4 Replies
Hi,
I did a test with this dummy data and I managed to do what I want by performina "GROUP BY" operation before doing the pivotation. Nevertheless when I do the same with the real data I receive the following message.
Expression.Error: The field 'X.PV' already exists in the record.
Details:
Name=X.PV
Value=However if I do the same using a Tag_ID columnn (a unique identifier for each tag) it work! Does anayone have a clue about what could be happening?
Regards,
Hi dariva,
Based on my test with your sample data, we can select the Tag column then click on Pivot Column button, set the returned values from Value column. For more information, see attached sample.
Best Regards,
Qiuyun Yuamitchandak v-qiuyu-msft
After I post the question I tried this operation with this mock data I presented. But whit the actual data I am having some weird behavior. I think it worth trying to explain it.
1) In the original dataset I have one entry every minute for each tag and each tag has as numerical (int) ID. If I use the ID instead of the tag the pivoting works perfectly. But using the tag (string) I receive that error message I show.
2) Doing some test I found the following. If I filter the time column in the data table to keep just some minutes the pivoting using the tag works! I increased the amount of time until I reached a limit of 55 minutes (+/-) when the problem start happening.
Finally I manage to obtain my desired result in a not very elegant way. I did the following.
1) I pivoted the data table in the using the time column2) I unpivoited the previous pivoted table
3) I them pivoted the previous unpivoted table using the tag column! (and it worked!!!).
Regards,
If the data type is number. You can use sum or max to group it in a matrix
