Skip to main content
Solved

Group-by and Increment in MDB (Non-Spatial)

  • November 24, 2015
  • 8 replies
  • 32 views
D
Forum|alt.badge.img
Hi,

 

 

I have an MDB file with a SourceID, its SequenceNumber and PositiveResponse as in the below image:

 

 

SourceID SeqNum PosRes

I want a new column OUTPUT having the sequence 1,2,3,4,5,...

 

But, the count should start where SeqNum=1 and PosRes=0.

 

So, it would be like-

 

SRCID SEQNR POSRES COUNT

 

5126350015700451 01 1 5

 

5126350015700451 02 1 6

 

5126350015700451 03 1 7

 

5126350015700451 04 1 8

 

5126350015700451 01 0 1

 

5126350015700451 02 0 2

 

5126350015700451 03 0 3

 

5126350015700451 04 0 4

 

 

I have tried using Sorter, Matcher, StatisticCalculator, Tester but I am not able to collect the output.

 

 

Thanks in advance.

 

Best answer by takashi

How about this?

Create a new sort key attribute with the AttributeCreator with conditional value setting.

_sortkey =

If POSRES = 1 Then @Value(SEQNR)

Else -@Value(SEQNR) <-- negative of SEQNR

Sort the records by

SRCID: Alphabetic Ascending

POSRES: Numeric Descending (or NEGRES Numeric Ascending)

_sortkey: Numeric Ascending

Then, add Updated_SEQNR with a Counter.

Finally re-sort by the original record number.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

8 replies

david_r
Celebrity
  • david_rCelebrity
  • November 24, 2015
Hi

 

 

Not sure I completely understand your requirement, but have a look at the Aggregator with a Group By clause.

 

 

David
takashi
Celebrity
  • takashiCelebrity
  • November 24, 2015
I guess it's a matter of the priority of sort keys. If my understanding is correct, a workaround is:

 

Sorter: Sort the records by SourceID, PosRes, SeqNum.

 

Counter: Add the COUNT attribute to the records grouping by SourceID.

 

- Counter Name: SourceID (attribute value)

 

- Count Start: 1

 

 

If you need to restore the original order of the records, insert another Counter before the Sorter to add sequential number to every record, and finally re-sort the records by the number.
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
mark2atsafe
Safer
Forum|alt.badge.img+59
  • mark2atsafeSafer
  • Safer
  • November 24, 2015
I guess it's a matter of the priority of sort keys. If my understanding is correct, a workaround is:

 

Sorter: Sort the records by SourceID, PosRes, SeqNum.

 

Counter: Add the COUNT attribute to the records grouping by SourceID.

 

- Counter Name: SourceID (attribute value)

 

- Count Start: 1

 

 

If you need to restore the original order of the records, insert another Counter before the Sorter to add sequential number to every record, and finally re-sort the records by the number.

I agree - the Sorter ought to do a lot of the work here. It might be the wrong sort priority, or maybe you need to strip the leading 0 from your seqnr, else FME might not be sorting it numerically.

FME Evangelist to the Rich and Famous!!
takashi
Celebrity
  • takashiCelebrity
  • November 25, 2015

I agree - the Sorter ought to do a lot of the work here. It might be the wrong sort priority, or maybe you need to strip the leading 0 from your seqnr, else FME might not be sorting it numerically.

In my observation, leading zero(s) seems to be ignored in numeric sorting with the Sorter.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
D
Forum|alt.badge.img
  • dewan
  • Author
  • November 27, 2015

Hi,

Thanks everyone.

I tried using both the ideas. Though, Sorter and Counter worked amazingly in the sequence, but what I got is a bit missing that what I want.

I got the Updated_SEQNR as the output. What I want is in the red numerics.

Actually, this is a requirement for road lanes, left and right. So, its like the number system.

takashi
Celebrity
  • takashiCelebrity
  • Best Answer
  • November 27, 2015

How about this?

Create a new sort key attribute with the AttributeCreator with conditional value setting.

_sortkey =

If POSRES = 1 Then @Value(SEQNR)

Else -@Value(SEQNR) <-- negative of SEQNR

Sort the records by

SRCID: Alphabetic Ascending

POSRES: Numeric Descending (or NEGRES Numeric Ascending)

_sortkey: Numeric Ascending

Then, add Updated_SEQNR with a Counter.

Finally re-sort by the original record number.

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
D
Forum|alt.badge.img
  • dewan
  • Author
  • November 27, 2015

Hi Takashi,

I tried your idea and its working perfectly as according to the image you shared, but The Updated_SEQNR column takes a simple count starting from 1. I didn't understand the meaning of the last two lines. Can you please suggest something on it.

Thanks

takashi
Celebrity
  • takashiCelebrity
  • November 27, 2015

Hi Takashi,

I tried your idea and its working perfectly as according to the image you shared, but The Updated_SEQNR column takes a simple count starting from 1. I didn't understand the meaning of the last two lines. Can you please suggest something on it.

Thanks

sorry for vague. See this screenshot.

> Then, add Updated_SEQNR with a Counter.

The Counter_2 in the screenshot adds the Updated_SEQNR number to the records. The number starts from 1 for each SRCID group, since the Counter Name is set to the value of SRCID.

> Finally re-sort by the original record number.

It's optional. If you need to restore the original order of the records, add temporary record ID to every record beforehand (the 1st Counter in the screenshot), and finally re-sort the records by the record ID (the Sorter_2).

Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?

Community Stats

32,154
Posts
122,043
Replies
39,812
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.