Solved

Transposing Data - Making Rows into Columns and Naming Columns Based on Attributes

R
Badge

Process_OverviewI'm attempting to transform the input Rain Gauge data set to the desired output as shown in the above inserted image but I'm at a loss on how to proceed?

 

The important factors are:

  • The time period is defined by the user, in this example 01/01/2022 - 10/01/2022 and the output table needs to include all days in that range.
  • The input Rain Gauge data only contains data for when it rained so not all dates are included.
  • I need to some how produce a column per Rain Gauge reference, then look up the associated rain fall value for each of the days and populate the new table structure.
  • The number of rain gauges can vary.

 

I have completed the process of creating a list of all dates for the requested time period.

 

I can't begin to fathom how to manipulate the data as shown and explained above.

If completing this in Excel I would have created a pivot to produce the table below:

 Example_Pivot 

Any help will be gratefully received!

 

Thank you.

icon

Best answer by geomancer 9 June 2022, 15:18

View original

6 replies

geomancer
Rank
Userlevel 4
Badge +36

Something like this?

RainGauges

R
Badge

Something like this?

RainGauges

Thanks for the amazing reply.

 

I can see in the first AttributeManager you've set up the Output Attributes as RG01 and RG02. If I had further RG's e.g. RG03, RG04 etc I have to manually add them to this transformer? Could that instead be automated by generating a unique list of RG references from the Input Data?

geomancer
Rank
Userlevel 4
Badge +36

The alternative (lower) route uses @Value(RG_Ref) to define attribute names. That creates unexposed attributes RG03, RG04 etc.

Afterwards you will have to explicitly expose those attributes. For this you can create a text document with all unique RG-values from the input file (take a look at the DuplicateFilter), and manually import this document in the AttributeExposer to expose all these attributes. Next you can process the whole input file.

R
Badge

The alternative (lower) route uses @Value(RG_Ref) to define attribute names. That creates unexposed attributes RG03, RG04 etc.

Afterwards you will have to explicitly expose those attributes. For this you can create a text document with all unique RG-values from the input file (take a look at the DuplicateFilter), and manually import this document in the AttributeExposer to expose all these attributes. Next you can process the whole input file.

Great idea, I used a DuplicateFilter to create a unique list of the RG_Refs in the CSV Reader. Then imported that into the AttributeExposer which works.

 

What about the AttributeManager on the upper route in the bookmark named - Define Attributes? Into this attributemanager I may need to add 50+ rain gauge attributes and set up the conditional formatting. Is there away of bulk completing that?

geomancer
Rank
Userlevel 4
Badge +36

Hi @rob_nason​ , the AttributeManager on the upper route in the bookmark named Define Attributes you can also perform an Import, but I suspect this will only import the attribute names, not the definitions of the conditional values.

If somebody has other ideas, please feel free to participate!

R
Badge

Hi @rob_nason​ , the AttributeManager on the upper route in the bookmark named Define Attributes you can also perform an Import, but I suspect this will only import the attribute names, not the definitions of the conditional values.

If somebody has other ideas, please feel free to participate!

The import function does indeed bring in the attribute names but I can't see a way of populating the conditional values other than by hand.

 

Perhaps a price I have to pay, your workflow is still a big improvement over the current Excel process!

Reply


Community Stats

28,707
Posts
109,472
Replies
36,550
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.