Skip to main content
  • EN

Hi,

I'm currently working on a project in which I need to change the table format from long to width. The data is coming from a csv file and consists of information on purchases. Each purchase has it's own line, so there are almost 1.7 million single rows in the dataset.

Currently each row has got the name of the product bought and the company who bought it. I would like to have a single row for each company, containing the number of items they bought from each category. My current table looks like this;

In which;

  • Deb.nr. Is a unique ID for each buying company
  • TotaalGroep , is the total number of items in the group, bought by all companies
  • Group is the group the item belongs to
  • Groep.total_count is the number of items within the group an individual company has bought
  • %vanGroepperDebNr. is the percentage of sold items a company has bought.

The other columns are seemingly irrelevant for now.

 

I would like for the data to look like this;

In which the % Total group is the percentage of %vanGroepperDebNr.

 

This way each company has his own record and the groups are listed after that.

For instance, company 1234 has bought 1 article of group 1, 3 of group 2 and 6 of group 3. The % gives information on how much of the total they have bought.

 

I've already tried several options, for instance with a aggregator. But they all didn't work.

Hopefully one of you can help me.

Thank you in advance!

 

 

Hi @jans

 

It appears that you are attempting Pivot table with Statistics, I would recommend this article as a starting point https://knowledge.safe.com/articles/19645/tutorial-manipulation-of-tabular-data.html

Hi @rahulsharma,

I've tried all options stated in the tutorial of the link. The problem seems to be within the aggregator, it created over 35 million unique values and I didn't seem to get them matched in to the right categories.

 

That's why I exported all my data to an Excel file, turned the table via R Studio and imported it back into FME again. Maybe not the most effective way but it works.

 

I would like to thank you for your help!

Reply


Community Stats

30,176
Posts
114,543
Replies
37,836
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.