Solved

Merging dynamic multiple lists

  • 1 October 2016
  • 9 replies
  • 23 views
F
Badge

Hi,

I have a couple of lists with dynamic size (e.g. _energyList, _powerList, _timeList, etc...). I would like to merge these lists together in order to get them prepared for inserting them into the MySQL database.

At the moment, I get the lists merged together indeed but in a sequence, perhaps the snap shot below could make it clearer:

And of course this is not what I'm trying to do. I need to get the _timeStamp and the _dataEnergy, etc... at the same row.

One possibility could be, to use ListExploder to each of the lists and insert each one of the items into its relevant column in the database separately. But this doesn't seem to be the right approach.

The goal at the end, is to create a list that is ready to be inserted into a database table that looks like the following:

I checked the thread below but it didn't do the trick.

https://knowledge.safe.com/questions/24685/merge-list-on-index.html

Does anyone maybe have an idea on how to get it to work?

Thanks a lot!

icon

Best answer by takashi 2 October 2016, 11:59

View original

9 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @fouly, if the source feature has multiple simple lists e.g. "observation_id{}", "station_id{}", "time{}", "date[]", etc, you can integrate them into a single structured (complex) list e.g. "_list{}.observation_id", "_list{}.station_id", "_list{}.time", "_list{}.date", etc. with a BulkAttributeRenamer:

  • Rename: All Attributes
  • Action: Regular Expression Replace
  • Mode: Rename
  • Text To Find: ^(.+)({\\d+})$
  • String: _list\\2.\\1

Then,

  1. AttributeExposer: Expose the list components - "_list{}.observation_id", "_list{}.station_id", "_list{}.time", "_list{}.date", etc.
  2. ListExploder: Explode the feature on the "_list{}".
F
Badge

In fact, the lists are stored in different features as follow:

what I am trying to do is to somehow glue them together and insert them into the database.

takashi
Rank
Userlevel 2
Badge +17

A suitable way should be considered depending on how the source data have been supplied.

If each source feature has an attribute containing just a single value for a single column and they are created for every row in the same sequence, it would not be necessary to take the trouble to create lists. Add destination row number to the features with Counters for each, and then merge all the features joining on the row number with a FeatureMerger. Send features for a representative column to the Requestor port; send others to the Supplier port, set the row number attribute to the Join On parameter, and set "Yes" to the "Process Duplicate Suppliers" parameter. That's it.

Or, if just one source feature is supplied per a single column and each feature has an attribute containing all values for a single column in a specific format (e.g. comma-separated), try the MultiAttributeSplitter from the FME Hub after merging the features unconditionally. The MultiAttributeSplitter can create a structured list from multiple attributes, and you can then apply the ListExploder. To merge the features unconditionally with a FeatureMerger, send a feature for a representative column to the Requestor port; send others to the Supplier port, set a constant value (e.g. 1) to the Join On for both Requestor and Supplier, and set "Yes" to the "Process Duplicate Suppliers" parameter.

F
Badge

The source data looks like the following:

<!-- Energy -->
var dataEnergyOut = [0.002,0.009,0.029,0.056,0.068,0.093,0.131,0.176,0.239,0.322,0.390,0.494,0.558,0.577,0.627,0.637,0.677,0.780,0.860,0.911,1.178,1.241,1.395,1.696,1.789,1.894,2.073,2.317,2.561,2.681,2.771,2.887,3.027,3.099,3.166,3.260,3.333,3.399,3.483,3.519,3.566,3.650,3.726,3.788,3.850,3.912,3.949,3.980,4.010,4.031,4.036,4.037,4.037];

<!-- Power -->
var dataPowerOut = [7,29,80,108,46,101,152,179,255,329,275,415,254,79,200,38,160,412,322,202,1070,249,619,1201,373,419,718,975,977,480,361,464,560,288,266,376,294,263,336,142,189,338,304,245,249,248,148,123,120,87,17,5,0];


<!-- Average -->
var dataPowerAvg = [null,28,80,108,48,100,152,180,252,332,272,416,256,76,200,40,160,412,320,204,1068,252,616,1204,372,420,716,976,976,480,360,464,560,288,268,376,292,264,336,144,188,336,304,248,248,248,148,124,120,84,20,4,0];

Regular expression is being used to extract each of them separately which ends up in having a list that looks like that:


//that's for the data energy out case
0.002,0.009,0.029,0.056,0.068,0.093,0.131,0.176,0.239,0.322,0.390,0.494,0.558,0.577,0.627,0.637,0.677,0.780,0.860,0.911,1.178,1.241,1.395,1.696,1.789,1.894,2.073,2.317,2.561,2.681,2.771,2.887,3.027,3.099,3.166,3.260,3.333,3.399,3.483,3.519,3.566,3.650,3.726,3.788,3.850,3.912,3.949,3.980,4.010,4.031,4.036,4.037,4.037

//that's for the data power out case
7,29,80,108,46,101,152,179,255,329,275,415,254,79,200,38,160,412,322,202,1070,249,619,1201,373,419,718,975,977,480,361,464,560,288,266,376,294,263,336,142,189,338,304,245,249,248,148,123,120,87,17,5,0

//and that's for the power average case
null,28,80,108,48,100,152,180,252,332,272,416,256,76,200,40,160,412,320,204,1068,252,616,1204,372,420,716,976,976,480,360,464,560,288,268,376,292,264,336,144,188,336,304,248,248,248,148,124,120,84,20,4,0

After that, AttributeSplitter transformer is being applied having ',' as the delimiter.

Hence, I obtain each of the variables as a list (of 52 elements in this use case). Afterwards, I would like to insert dataEnergyOutList[0], dataPowerOut[0], powerAverage[0], etc... in the first row of a specific table (each one of them in their relevant cell) in the database. That's why I thought of a merger or something like that.

What I wanted to avoid is having an insert statement of each elements separately (I didn't try it out though).

I hope I could explain that scenario in a clear enough way. 

takashi
Rank
Userlevel 2
Badge +17

If three features having those CSV texts are supplied separately, the second approach I suggested in the previous post can be applied. i.e. FeatureMerger - MultiAttributeSplitter - ListExploder. See also the attached example (FME 2016.1.2): multiattributesplitter-example.fmw

Alternatively, you can still apply the BulkAttributeRenamer approach I mentioned at first, if you split the CSV texts with AttributeSplitters for each and then merge them unconditionally with the FeatureMerger.

Anyway, the point is to merge the multiple features unconditionally with a FeatureMerger.

takashi
Rank
Userlevel 2
Badge +17

If three features having those CSV texts are supplied separately, the second approach I suggested in the previous post can be applied. i.e. FeatureMerger - MultiAttributeSplitter - ListExploder. See also the attached example (FME 2016.1.2): multiattributesplitter-example.fmw

Alternatively, you can still apply the BulkAttributeRenamer approach I mentioned at first, if you split the CSV texts with AttributeSplitters for each and then merge them unconditionally with the FeatureMerger.

Anyway, the point is to merge the multiple features unconditionally with a FeatureMerger.

There could be several ways. This workflow is also possible, and it might be closest to your thought. FYI.

 

itay
Rank
Badge +16

Hi @fouly, I totally agree with @takashi, the key is to unconditionally merge the incomming features.

F
Badge

Hi @takashi,

Thanks a lot! This worked just great!

Since you've been following my posts, I'm applying it to the rest of the PV readings and storing them in MySQL database after. I will be uploading the whole workbench as soon as I'm done with it, just in case if somebody tackled any of these issues.

Thanks again!

G
Badge +3

OR..

After the listexploders in takashi 2nd example you can create a list grouped by element_index.

Use stringconcatenator to concatenate the list elements, inserting a comma.

Then an attributesplitter.

Attributecreator for each listelement.

No need to create a cartesian product..

Reply


Community Stats

28,681
Posts
109,406
Replies
36,540
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.