Can i create a reverse pivot table?

Hello @reneewsp

There is a interesting link recorded video about pivot :

https://knowledge.safe.com/articles/31701/transpose-a-table-using-fme.html

Thanks,

Danilo

Thanks @danilo_fme - yes I did read this but I couldn't work out how to "reverse" pivot - i.e. create a table with the fields District, Industry and year. I'll have another go.

The screenshot below illustrates a possible way I can think of. Assuming that the years are consecutive integers. The regular expression ^\d{4}$ matches a string consisting of just four numbers.

Result:

Alternatively this Python script works as well.

# PythonCaller Script Example
class ReversePivoter(object):
    def __init__(self):
        pass
        
    def input(self, feature):
        beg, end = 2017, 2021
        years, values = [], []
        for y in range(beg, end + 1):
            year = str(y)
            years.append(year)
            values.append(feature.getAttribute(year))
            feature.removeAttribute(year)
            
        for year, value in zip(years, values):
            newFeature = feature.clone()
            newFeature.setAttribute('Year', year)
            if value != None:
                newFeature.setAttribute('Value', value)
            self.pyoutput(newFeature)
        
    def close(self):
        pass

Hope this helps.

takashi wrote:

The screenshot below illustrates a possible way I can think of. Assuming that the years are consecutive integers. The regular expression ^\d{4}$ matches a string consisting of just four numbers.

 

Result:

 

Alternatively this Python script works as well.

# PythonCaller Script Example
class ReversePivoter(object):
    def __init__(self):
        pass
        
    def input(self, feature):
        beg, end = 2017, 2021
        years, values = [], []
        for y in range(beg, end + 1):
            year = str(y)
            years.append(year)
            values.append(feature.getAttribute(year))
            feature.removeAttribute(year)
            
        for year, value in zip(years, values):
            newFeature = feature.clone()
            newFeature.setAttribute('Year', year)
            if value != None:
                newFeature.setAttribute('Value', value)
            self.pyoutput(newFeature)
        
    def close(self):
        pass

 

Hope this helps.

Yes, ListExpressionPopulator or ListPopulator are the transformers I would use too as the dedicated FME Transformers for "Reversed" pivots.

It's also possible to use an attributeexploder

Thanks @ebygomm - that works beautifully simply, and thanks to @takashi 's answer i know what the ^\\d{4}$ means too. Brilliant, thanks all!