Solved

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

  • 13 October 2022
  • 4 replies
  • 3 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

icon

Best answer by markatsafe 13 October 2022, 17:57

View original
1 Attachment

4 replies

I

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

1 Attachment
ctredinnick
Rank
Userlevel 3
Badge +16

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
Badge +2

@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

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 :)

Reply


Community Stats

28,723
Posts
109,512
Replies
36,560
Members
Terms & ConditionsCookie settings
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.