Question

Transposing attributes like Excel does

  • 11 April 2016
  • 5 replies
  • 0 views

Badge

Hello,

I am reading a folder containing various Excel files of the same schema. The difference between the files is the date and the content, the structure is always the same. My goal is to create a line diagram in a new Excel file. I want to use the "ChartGenerator".

My Problem now is to prepare the data to be used within the ChartGenerator:

I will have a bunch of records as illustrated in the "Start" section of the above pic. For every additional date I will have an additional record. I need to convert the data as illustrated in the "End" section. For every additional date I will need an additional column.

The various existing Posts regarding transposing data do not seem to tackle my issue.

Any ideas?


5 replies

Userlevel 4

Hi

Have you looked at the AttributePivoter?

David

Badge +3

There are a lot of posts on the forum showing soltuions.

Pivoter is ..usable

You still have to expose the attributes.

Best use a attribute exploder, remove fme-attributes, expose xls_row id.

Create a csv or txt-file.

Examples of this on forum as wel.

Badge +2

Hi,

Alternatively, you can merge all the data by adding rows for each date then do transpose in one-shot for entire data as shown in below link

https://knowledge.safe.com/articles/1315/advanced-...

Pratap

Badge

Hi,

I have followed the "vegetable" example in the above link. It works nice. However, if I understood it correctly, it requires knowing the number of records before the script is run to properly feed the attribute creator. I adapted it to my case with , at the Moment, 4 files and it works nice:

The resulting file looks like this:

However, I will have more files to process (by the time of the script run I will not know how many!), each of which will require an additional column with a date value! This seems requireing a loop approach. I tried to dig the "ListPivot.fmx" of the Forum, but I am not successfull.

I played around with the attributepivoter, but it does not bring me any further.

Some more ideas?

Badge

@femo, did you figure out a way to loop this process? I'm trying to do something similar, but I will not know how many date value columns I will need to add. Thanks!

Reply