Question

Transpose - but remove some columns and insert blank values with 0

  • 8 August 2018
  • 7 replies
  • 2 views
R
Badge

Hi folks,

This may be an easy question but it's been stumping me for a little while now. I have an excel sheet with the following format (this is just a test dataset but with a similar format to a larger excel file I have):

AnimalTypeYearAmountDeerIndem2001100DeerClaim2003200ElkIndem2001111ElkIndem2004222WaterfowlClaim2005333WaterfowlIndem2007444

And what I want as a final table is the following:

AnimalType2001200220032004200520062007DeerIndem100000000DeerClaim002000000ElkIndem11100222000ElkClaim0000000WaterfowlIndem000000444WaterfowlClaim000033300

I've used an AttributePivoter and everything seems to work ok, but instead of the zeroes in the final table, I am just left with blanks where there was no data in the original table. But I can't seem to figure out how to edit any of the data that is getting output after the AttributePivoter. My workspace screenshot is below, I hope someone might be able to help me out!!

7 replies

H
Badge +2

Hi @ryanfishersk,

You should be able to use the NullAttributeMapper after the Data output and under the Map To parameters set the parameters like below:

This should replace the blanks with a value of zero.

When you say you can't edit the data after the AttributePivoter - have you already tried this method and you are receiving an error?

R
Badge

Hi @ryanfishersk,

You should be able to use the NullAttributeMapper after the Data output and under the Map To parameters set the parameters like below:

This should replace the blanks with a value of zero.

When you say you can't edit the data after the AttributePivoter - have you already tried this method and you are receiving an error?

Hi @hollyatsafe I tried the NullAttributeMapper and I tried selecting "All Attributes" but I still end up with blanks where the zeroes should be. I also tried changing the "Map" option to "Selected Attributes", but none of the newly create columns from the AttributePivoter (e.g., 2003, 2004, 2005, etc.) show up to be selected.

H
Badge +2

Hi @hollyatsafe I tried the NullAttributeMapper and I tried selecting "All Attributes" but I still end up with blanks where the zeroes should be. I also tried changing the "Map" option to "Selected Attributes", but none of the newly create columns from the AttributePivoter (e.g., 2003, 2004, 2005, etc.) show up to be selected.

Hi @ryanfishersk,

 

What version of FME are you using, are you able to send a copy of the workspace so I can investigate this?

 

R
Badge

Hi @ryanfishersk,

You should be able to use the NullAttributeMapper after the Data output and under the Map To parameters set the parameters like below:

This should replace the blanks with a value of zero.

When you say you can't edit the data after the AttributePivoter - have you already tried this method and you are receiving an error?

hi @hollyatsafe I'm using Workbench 2017.1.2.1 32bit. I've attached the workspace and the excel file I've been working with!

H
Badge +2

hi @hollyatsafe I'm using Workbench 2017.1.2.1 32bit. I've attached the workspace and the excel file I've been working with!

Hi @ryanfishersk,

 

Ah yes I understand what is going on now. I think the problem is because you are manually creating the Year columns in the Excel writer - this is when the <missing> values are created and therefore cannot be edited using transformers prior to the writer.

 

Instead I think the best option would be to use an Attribute Manager or Creator to add in these attributes and you can then either use Conditional values within this transformer to say if 200x 'value is missing' set to 0), Else leave as is. Alternatively you could create the new attributes and then use the NullAttributeMapper with the settings above instead of Conditional statements.

 

 

takashi
Rank
Userlevel 2
Badge +17

Hi @ryanfishersk,

You should be able to use the NullAttributeMapper after the Data output and under the Map To parameters set the parameters like below:

This should replace the blanks with a value of zero.

When you say you can't edit the data after the AttributePivoter - have you already tried this method and you are receiving an error?

Hi @ryanfishersk, you have to expose the attribute names - "2001", "2002", ... "2007" with the AttributeExposer, then select them in the "Selected Attributes" parameter of the NullAttributeMapper (Map: Selected Attributes).

 

takashi
Rank
Userlevel 2
Badge +17

Hi @ryanfishersk,

You should be able to use the NullAttributeMapper after the Data output and under the Map To parameters set the parameters like below:

This should replace the blanks with a value of zero.

When you say you can't edit the data after the AttributePivoter - have you already tried this method and you are receiving an error?

This is a workspace example that would generate your desired result.

 

transpose-2.fmwt (FME 2017.1.2.1)

Reply


Community Stats

28,724
Posts
109,518
Replies
36,563
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.