Skip to main content
Solved

Populating unique values for multiple attributes and writing to excel

  • October 10, 2021
  • 2 replies
  • 109 views
topotoma
Contributor
Forum|alt.badge.img+3

Hello,

 

Let's say i have an excel table containing multiple attributes and features, and I would like to output in an excel table a summary of the unique values for each attributes.

 

Using duplicate filters I managed to get the results but they are ordered in rows in order of arrival instead of being nested for each attribute.

 

Example with three attributes:

ID ----- Name ------City

A1 -----Mark -------Moscow

A2 ------Paul -------Glasgow

A1 -----Joanna ---Capetown

A3------ Paul -------Glasgow

 

 

Then in excel I get three columns but the results are spread in the order of arrival of results for each column unique values:

 

Column A -- Column B -- Column C

A1

A2

A3

--------------Joanna

--------------Paul

--------------Mark

--------------------------------Glasgow

---------------------------------Moscow

-------------------------------- Capetown

 

Instead of having the results over various lines, I would like to get the unique values on top of each column. Is there a way to do so ?

Maybe working with lists ?

 

Many thanks if you can help me. Best regards.

Thomas

 

Best answer by caracadrian

You have to get a little creative.

I think you just need some lists of unique values for any column but you don't need any correspondence for them. For example A2 ------Paul -------Glasgow is different from A3------ Paul -------Glasgow because of the ID. But you want to ignore that.

Just remove duplicates in parallel for each column, cleanup by removing the other attributes, count each parallel stream locally and join them by the _count attribute.

I have attached a sample workspace that processes your example.

View original
Did this help you find an answer to your question?

2 replies

caracadrian
Contributor
Forum|alt.badge.img+23
  • caracadrianContributor
  • Contributor
  • Best Answer
  • October 11, 2021

You have to get a little creative.

I think you just need some lists of unique values for any column but you don't need any correspondence for them. For example A2 ------Paul -------Glasgow is different from A3------ Paul -------Glasgow because of the ID. But you want to ignore that.

Just remove duplicates in parallel for each column, cleanup by removing the other attributes, count each parallel stream locally and join them by the _count attribute.

I have attached a sample workspace that processes your example.

topotoma
Contributor
Forum|alt.badge.img+3
  • topotomaContributor
  • Author
  • Contributor
  • October 11, 2021

Hello Caracadrian,

This is great, i didn't think about using the individual counter to order the features in the end, great job !

Many thanks for your help and by also even preparing a workspace to explain your solution.

This will help me in creating catalogs of features per attributes and check if everything is entered correctly in the database.

Best regards.

Thomas

Reply


Helpful Members This Week

Community Stats

31,018
Posts
117,535
Replies
38,794
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.