Create values from Excel Column headings

Hi,

I am seeking advice on the following transformation. In short we need the column headings to become part of the row data so that we can eventually push to a database table and have the column headings "FXA" and "FXB" in this case be a code that can be associated with the row data.

Sample Source Excel Layout

The end result will need to look like

Thanks

please see attached a working example

It is using UnPivot transformation
www.etl-tools.com/wiki/aetle/processing_...working_with_unpivot

Here are some screenshots fro curious users

Thanks Mike. I have seen this example and did use it to get a result. But to be honest I am not exactly sure how and that stems from a lack of understanding of what the unpivot setup screens are asking for and how it is pieced together. There needs to be more words around the screen shots explaining what is needed and the various options that are available.

Also, in our case the GROUPS or the column headings could change without us knowing and thus break the transformation with hardcoded group names. We need this to be dynamic

Thanks. These are actually screen shots I sent to a coworker of my working sample. But I was unable to explain to her in any detail what is actually happening and why it worked. Plus as mentioned our column headings will probably get changed by the owner of the source spreadsheet and we don't want to keep changing the transformation.

Is there a way to dynamically create the pivot groups? in our case and these screenshots FXA and FXB

Hi Bruce

Totally agree with you the documentation has plenty of room for improvement.
(We are not the only ones in this club)

Yes, It is possible to work with files with dynamic fields positions using Field Value transformation function
www.etl-tools.com/wiki/aetle/transformat...ions/miscellaneous?s []=field&s[]=value
I have attached another example for you

In my opinion, constantly shifting fields is a data quality issue and should be addressed at the source.