Skip to main content
Question

duplicates + aggregator

  • July 5, 2017
  • 4 replies
  • 194 views
K
Forum|alt.badge.img

Hello,

I have four records with three attributes. Their values are:

GB172783.TXT PAG 8138

GB172783.TXT PAG 8135

GB467715.TXT SNC 4129

GB467715.TXT PAG 4131

I want to merge the first + second to:

GB172783.TXT PAG 8135, 8138

and the third+fourth to:

GB467715.TXT SNC, PAG 4129, 4131

I use the aggregator and merge (concatenate) the last attribute grouped by the first attribute. How I can set the second attribute? In the case of the 3rd + 4th I want to keep both (SNC, PAG), for the 1st + 2nd I only want to keep the value PAG once and not PAG, PAG. Thanks!

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.

4 replies

erik_jan
Contributor
Forum|alt.badge.img+26
  • erik_janContributor
  • Contributor
  • July 5, 2017

You can select both the second and third attributes for concatenation.

Then to remove duplicate use the AttributeSplitter, ListDuplicateRemover and ListConcatenator in sequence.

Not a really charming way, but it works.

erik_jan
Contributor
Forum|alt.badge.img+26
  • erik_janContributor
  • Contributor
  • July 5, 2017

You can select both the second and third attributes for concatenation.

Then to remove duplicate use the AttributeSplitter, ListDuplicateRemover and ListConcatenator in sequence.

Not a really charming way, but it works.

I created a little example.

 

example.fmw

 

takashi
Celebrity
  • takashiCelebrity
  • July 10, 2017

I recently discovered an interesting usage of XQeury expressions with a JSONTemplater and this might be a typical case where the usage can be applied effectively. I would like to share the potential with all of you.

See this workspace example: aggregate-records-with-json.fmwt (FME 2017.0)

0684Q00000ArLCDQA3.png

JSONTemplater ROOT Template Expression: Aggregate the XML elements created by the SUB expression grouping by field1, and return a JSON object which contains desired values created with XQuery functions.

let $t := <t>{fme:process-features("SUB")}</t>return {    "field1": fme:get-attribute("field1"),    "field2": fn:string-join(fn:distinct-values({for $v in $t/s/@f2 return $v}), ","),    "field3": fn:string-join(fn:distinct-values({for $v in $t/s/@f3 return $v}), ",")}

JSONTemplater SUB Template Expression: Create an XML element containing the values of field2 and field3 as XML attributes for each record.

<s f2="{fme:get-attribute("field2")}" f3="{fme:get-attribute("field3")}" />
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
takashi
Celebrity
  • takashiCelebrity
  • July 10, 2017

I recently discovered an interesting usage of XQeury expressions with a JSONTemplater and this might be a typical case where the usage can be applied effectively. I would like to share the potential with all of you.

See this workspace example: aggregate-records-with-json.fmwt (FME 2017.0)

0684Q00000ArLCDQA3.png

JSONTemplater ROOT Template Expression: Aggregate the XML elements created by the SUB expression grouping by field1, and return a JSON object which contains desired values created with XQuery functions.

let $t := <t>{fme:process-features("SUB")}</t>return {    "field1": fme:get-attribute("field1"),    "field2": fn:string-join(fn:distinct-values({for $v in $t/s/@f2 return $v}), ","),    "field3": fn:string-join(fn:distinct-values({for $v in $t/s/@f3 return $v}), ",")}

JSONTemplater SUB Template Expression: Create an XML element containing the values of field2 and field3 as XML attributes for each record.

<s f2="{fme:get-attribute("field2")}" f3="{fme:get-attribute("field3")}" />
Another thought. Looks like simpler.

 

0684Q00000ArMhtQAF.png

 

JSONTemplater ROOT Template Expression:

 

{
    "field1": fme:get-attribute("field1"),
    "field2": fn:string-join(fn:distinct-values(fme:get-list-attribute("_list{}.field2")), ","),
    "field3": fn:string-join(fn:distinct-values(fme:get-list-attribute("_list{}.field3")), ",")
}<br>

 

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

Community Stats

32,431
Posts
123,238
Replies
40,557
Members
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.