I have a data set with multiple repeated values such as this
ADA
ADA
ADA
Benton
Canyon
Canyon
Dearborn
Dearborn
Dearborn
Dearborn
and I want to increment a count every time the preceding value is different than the next value.
so my 'count' would be like
1 - ADA
1 ADA
1 - ADA
2 - Benton
3 - Canyon
3 - Canyon
4 - Dearborn
4 - Dearborn
4 - Dearborn
4 - Dearborn
Best answer by bwn
@patmhid We don't have anything out of the bx. If you can order your features by the grouping attribute, then you can use the Adjacent Feature option in AttributeCreator to create group counts. I've attached an example workspace.
Similarly, that's the Solution I use in my Workspaces: Sorter + AttributeCreator
AttributeCreator
Enable Adjacent Feature Attributes: On
Prior Features: 1
Conditional Value: If @Value(GroupAttribute)=@Value(feature[-1].GroupAttribute) Then @Value(feature[-1].GroupID) Else @Value(feature[-1].GroupID)+1
The only thing I found was that at least in 2018.1, the FME Workspace Editor wants you to create the GroupID Attribute with a separate, preceding AttributeCreator first that initialises it to a default Dummy Value, otherwise an AttributeCreator that simultaneously tries to use Adjacent Feature Handling and create a new Attribute that doesn't yet exist in the Schema and is calculated using an Adjacent Feature Value will report itself as an "Invalid Transformer" in the workspace, even though it WILL run fine when executing the workflow!
+47You could use an Aggregator to merge on that attribite (add all other attributes into a list) the a normal counter. After that use a list explode to get back to the original.
Alternatively you could use a sampler to get the first feature for each of the attributes you want to count and then count the features, you can then use a FeatureJoiner to join the count attribute back to the rest of the data.
I'm not sure if either of these are the best option, but they will work.
@patmhid We don't have anything out of the bx. If you can order your features by the grouping attribute, then you can use the Adjacent Feature option in AttributeCreator to create group counts. I've attached an example workspace.
+26@patmhid We don't have anything out of the bx. If you can order your features by the grouping attribute, then you can use the Adjacent Feature option in AttributeCreator to create group counts. I've attached an example workspace.
Similarly, that's the Solution I use in my Workspaces: Sorter + AttributeCreator
AttributeCreator
Enable Adjacent Feature Attributes: On
Prior Features: 1
Conditional Value: If @Value(GroupAttribute)=@Value(feature[-1].GroupAttribute) Then @Value(feature[-1].GroupID) Else @Value(feature[-1].GroupID)+1
The only thing I found was that at least in 2018.1, the FME Workspace Editor wants you to create the GroupID Attribute with a separate, preceding AttributeCreator first that initialises it to a default Dummy Value, otherwise an AttributeCreator that simultaneously tries to use Adjacent Feature Handling and create a new Attribute that doesn't yet exist in the Schema and is calculated using an Adjacent Feature Value will report itself as an "Invalid Transformer" in the workspace, even though it WILL run fine when executing the workflow!
+44@patmhid We don't have anything out of the bx. If you can order your features by the grouping attribute, then you can use the Adjacent Feature option in AttributeCreator to create group counts. I've attached an example workspace.
I thought after yesterday's webinar you'd be recommending the InlineQuerier @Mark Stoakes :-)
select A.*, B.Rownum as Count from Table1 A,
(SELECT
ROW_NUMBER () OVER (
ORDER BY "Name"
) RowNum,
"Name"
from
(select distinct("Name") from
Table1))B
where A."Name" = B."Name"I've actually got a python based customer transformer that does the same thing that i really ought to get put on the hub
You could use an Aggregator to merge on that attribite (add all other attributes into a list) the a normal counter. After that use a list explode to get back to the original.
Alternatively you could use a sampler to get the first feature for each of the attributes you want to count and then count the features, you can then use a FeatureJoiner to join the count attribute back to the rest of the data.
I'm not sure if either of these are the best option, but they will work.
Thank you. I tried this and it worked, but I am going to try the Adjacent Feature option in AttributeCreator to see if that is closer to what I will need going forward.
@patmhid We don't have anything out of the bx. If you can order your features by the grouping attribute, then you can use the Adjacent Feature option in AttributeCreator to create group counts. I've attached an example workspace.
Thanks, this did works great.
Similarly, that's the Solution I use in my Workspaces: Sorter + AttributeCreator
AttributeCreator
Enable Adjacent Feature Attributes: On
Prior Features: 1
Conditional Value: If @Value(GroupAttribute)=@Value(feature[-1].GroupAttribute) Then @Value(feature[-1].GroupID) Else @Value(feature[-1].GroupID)+1
The only thing I found was that at least in 2018.1, the FME Workspace Editor wants you to create the GroupID Attribute with a separate, preceding AttributeCreator first that initialises it to a default Dummy Value, otherwise an AttributeCreator that simultaneously tries to use Adjacent Feature Handling and create a new Attribute that doesn't yet exist in the Schema and is calculated using an Adjacent Feature Value will report itself as an "Invalid Transformer" in the workspace, even though it WILL run fine when executing the workflow!
Thanks this is perfect!
I thought after yesterday's webinar you'd be recommending the InlineQuerier @Mark Stoakes :-)
select A.*, B.Rownum as Count from Table1 A,
(SELECT
ROW_NUMBER () OVER (
ORDER BY "Name"
) RowNum,
"Name"
from
(select distinct("Name") from
Table1))B
where A."Name" = B."Name"I've actually got a python based customer transformer that does the same thing that i really ought to get put on the hub
@ebygomm Haha... well I was just about to suggest that when the webinar ended unexpectedly!
+26I thought after yesterday's webinar you'd be recommending the InlineQuerier @Mark Stoakes :-)
select A.*, B.Rownum as Count from Table1 A,
(SELECT
ROW_NUMBER () OVER (
ORDER BY "Name"
) RowNum,
"Name"
from
(select distinct("Name") from
Table1))B
where A."Name" = B."Name"I've actually got a python based customer transformer that does the same thing that i really ought to get put on the hub
Small performance optimisation, particularly in SQLite based InlineQuerier.
Instead of:
Use
Whilst they produce the same result, the query optimiser and the SQLite engine will generally execute faster with the query plan generated by GROUP BY.
Group Counter. FME 2021.1 Counter has been upgraded to include a group count.
New Counter dialog:
results:
