Identifying source column of Pivot data

patonp · Post by **patonp** » Tue Apr 28, 2009 11:44 am

I realize that similar questions have been posted about the Pivot stage, but the situation I'm looking at is slightly unique...

We have an inbound file with over 50 occurrences of a particular field. We would like to normalize this data into separate rows, and it would seem that the Pivot stage is the obvious choice to accomplish this. However, we would also like to know in which column the data has originated. So far this is consistent with other posts I've seen on the site...

However, the naming convention for the inbound columns is consistent, and follows the format VAL01, VAL02, VAL03....VAL50. Basically, each column begins with the text VAL and is appended with a two digit offset.

Is there a function within the Pivot stage that allows one to identify which occurrence of a given field was the source of data?

I've seen one workaround that suggests adding a column containing a field name for each pivot field. However, I'm hoping that there might be a feature that allows me to identify the source column as a numeric offset. Based on the naming of the source fields, re-constructing the source column names would then be quite simple. Am I hoping for too much here?

ray.wurlod · Post by **ray.wurlod** » Tue Apr 28, 2009 4:34 pm

Curiously I answered this exact question yesterday.
viewtopic.php?t=126771