Question

duplicates + aggregator


Badge

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!


4 replies

Userlevel 2
Badge +12

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.

Userlevel 2
Badge +12

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

 

Userlevel 2
Badge +17

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")}" />
Userlevel 2
Badge +17

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>

 

Reply