Skip to main content
Solved

Pivot SQL table for large amounts of data, lists, attribute pivoter, feature joiner or SQL executor

  • October 13, 2022
  • 4 replies
  • 45 views
I

Hi,

 

I have a problem that is bugging the *** out of me. By know it's a question of FME pride since I assured my colleague that FME was the way for this :)

 

I have a rather sizeable SQL table containing measurement data from gauges. It is listed in a fairly common way (described by the attached picture). For analytical pictures I would like it to turn the attribute "name" inte columns that have their value displayed for each time step. This is for data analytical reasons.

 

I have tried with attribute pivoter without success, working with lists, creating a structured table than make a join. The problem is that the amount of names is so large that I can't make any manual type-ins for name etc. It has to be dynamically otherwise it won't work

 

Please, any suggestions or will I have to admit defeat and hand it over to a sql colleague. It will give him alot of work (and also he will laugh in an evil way).

 

Thanks in advance

Best answer by markatsafe

@ingemar_2020​ Can you use Aggregator and Group By the Start / End Date. ? Set the Aggregation Mode ; Attributes Only and Attribute Accumulation Mode: Merge Attributes

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

I
  • ingemar_2020
  • Author
  • 3 replies
  • October 13, 2022

I have managed to create a table with the attribute names (Duplicate filter for the names, attribute creator with value -->@name, then an attribute exposer) and I think I have the proper values in a list) but I am not shure this is the correct way to go about things

ctredinnick
Supporter
Forum|alt.badge.img+19
  • ctredinnickSupporter
  • Supporter
  • 225 replies
  • October 13, 2022

You can do this with three transformers. The trick is you can create an attribute with the name of another attribute's value.

Create an attribute where the name of the attribute is the Name, and the value is your measured value

imageThen use an Aggregator, with Group By on Start Date and End Date, set to Merge Incoming Attributes.

Then lastly, you just need to expose the PAA-EE-YY, PAA-EE-ZZ, PAA-FF-GG attributes you created in the first step.

M
Forum|alt.badge.img+2
  • markatsafe
  • 1891 replies
  • Best Answer
  • October 13, 2022

@ingemar_2020​ Can you use Aggregator and Group By the Start / End Date. ? Set the Aggregation Mode ; Attributes Only and Attribute Accumulation Mode: Merge Attributes

I
  • ingemar_2020
  • Author
  • 3 replies
  • October 14, 2022

Thank you for the reply, to put "measured value" in the attribute Value was what I was missing. I actually was closer than I thought but due to restraint in my working data (select top 100000 in sql) I hade start date / end date values that was scattered. On the next chain in this workspace :)

Community Stats

32,061
Posts
121,727
Replies
39,688
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.