I have a spreadsheet with three sets of seven fields that need to be merged into three and make sure that the values from the other fields match up. For example, I have 7 ID Fields that have 7 Quantity fields and 7 Price Fields. These need to all merged into one ID field and one price field and one quantity field. I am not sure how to go about it other than copying and psting all the values into one field in excel. Any suggestion would be great. Thanks
Hello @piercexc, the transformer Aggregator is not enough for you in this case? After the Reader Excel file you can work with this Transformer Aggregator by ID.
Regards,
Danilo
How are the fields supposed to be merged? All the ID fields for a single row will be either null or the same value, or is there a possibility that Name 1 and Name 2 will have different values?
If the prices are different do you want the min/max or some sort of average?
Should the Quantity fields be summed?
Should the data between rows be accumulated (ie two rows with the same ID)?
If you have one record with name1 name2 name3 quantity1 quantity2 quantity3 etc clearly this would be easier to work with if you could split this up into multiple records with just one name and quantity field on each record.
There are 3 options I could see to address this:First Approach: 1. use AttributeExploder to get them into separate features. 2. use an AttributeCreator with conditional value using contains 1, contains 2 etc to create a new field called record number. 3. group fields by that record number - Aggregator group by with list 4. use FeatureBuilder from the hub to use those lists to generate field values by record.Second approachHello @piercexc, the transformer Aggregator is not enough for you in this case? After the Reader Excel file you can work with this Transformer Aggregator by ID.
Regards,
Danilo
This is the final outcome I am looking for. thanks
Hi @piercexc, if each field name consists of non-digits characters and one of 1 to 7 which should be referenced to identify a set of related fields , this approach is also possible.
This is a prototype in a case where the range of the number is 1 to 4.
BuliAttributeRenamer parameters:
ooh, I cannot stay still without trying XML and/or JSON...
XML:
XMLTemplater Expression:
<root>{
for $i in (1 to 4)
return
<array>
<Name>{fme:get-attribute(fn:concat("AA", $i))}</Name>
<Quantity>{fme:get-attribute(fn:concat("B", $i, "B"))}</Quantity>
<Price>{fme:get-attribute(fn:concat($i, "CC"))}</Price>
</array>
}</root>
yAddition] If you aren't familiar with XQeury, this basic template expression can also be possible.
<root>
<array>
<Name>{fme:get-attribute("AA1")}</Name>
<Quantity>{fme:get-attribute("B1B")}</Quantity>
<Price>{fme:get-attribute("1CC")}</Price>
</array>
<array>
<Name>{fme:get-attribute("AA2")}</Name>
<Quantity>{fme:get-attribute("B2B")}</Quantity>
<Price>{fme:get-attribute("2CC")}</Price>
</array>
<array>
<Name>{fme:get-attribute("AA3")}</Name>
<Quantity>{fme:get-attribute("B3B")}</Quantity>
<Price>{fme:get-attribute("3CC")}</Price>
</array>
<array>
<Name>{fme:get-attribute("AA4")}</Name>
<Quantity>{fme:get-attribute("B4B")}</Quantity>
<Price>{fme:get-attribute("4CC")}</Price>
</array>
</root>
JSON:
JSONTemplater Expression:
d
{
"Name":fme:get-attribute("AA1"),
"Quantity":fme:get-attribute("B1B"),
"Price":fme:get-attribute("1CC")
},
{
"Name":fme:get-attribute("AA2"),
"Quantity":fme:get-attribute("B2B"),
"Price":fme:get-attribute("2CC")
},
{
"Name":fme:get-attribute("AA3"),
"Quantity":fme:get-attribute("B3B"),
"Price":fme:get-attribute("3CC")
},
{
"Name":fme:get-attribute("AA4"),
"Quantity":fme:get-attribute("B4B"),
"Price":fme:get-attribute("4CC")
}
]
QUpdate] This expression is also available.
P
for $i in (1 to 4) return
{
"Name": fme:get-attribute(fn:concat("AA", $i)),
"Quantity": fme:get-attribute(fn:concat("B", $i, "B")),
"Price": fme:get-attribute(fn:concat($i, "CC"))
}
]
Result:
ooh, I cannot stay still without trying XML and/or JSON...
XML:
XMLTemplater Expression:
<root>{
for $i in (1 to 4)
return
<array>
<Name>{fme:get-attribute(fn:concat("AA", $i))}</Name>
<Quantity>{fme:get-attribute(fn:concat("B", $i, "B"))}</Quantity>
<Price>{fme:get-attribute(fn:concat($i, "CC"))}</Price>
</array>
}</root>
yAddition] If you aren't familiar with XQeury, this basic template expression can also be possible.
<root>
<array>
<Name>{fme:get-attribute("AA1")}</Name>
<Quantity>{fme:get-attribute("B1B")}</Quantity>
<Price>{fme:get-attribute("1CC")}</Price>
</array>
<array>
<Name>{fme:get-attribute("AA2")}</Name>
<Quantity>{fme:get-attribute("B2B")}</Quantity>
<Price>{fme:get-attribute("2CC")}</Price>
</array>
<array>
<Name>{fme:get-attribute("AA3")}</Name>
<Quantity>{fme:get-attribute("B3B")}</Quantity>
<Price>{fme:get-attribute("3CC")}</Price>
</array>
<array>
<Name>{fme:get-attribute("AA4")}</Name>
<Quantity>{fme:get-attribute("B4B")}</Quantity>
<Price>{fme:get-attribute("4CC")}</Price>
</array>
</root>
JSON:
JSONTemplater Expression:
d
{
"Name":fme:get-attribute("AA1"),
"Quantity":fme:get-attribute("B1B"),
"Price":fme:get-attribute("1CC")
},
{
"Name":fme:get-attribute("AA2"),
"Quantity":fme:get-attribute("B2B"),
"Price":fme:get-attribute("2CC")
},
{
"Name":fme:get-attribute("AA3"),
"Quantity":fme:get-attribute("B3B"),
"Price":fme:get-attribute("3CC")
},
{
"Name":fme:get-attribute("AA4"),
"Quantity":fme:get-attribute("B4B"),
"Price":fme:get-attribute("4CC")
}
]
QUpdate] This expression is also available.
P
for $i in (1 to 4) return
{
"Name": fme:get-attribute(fn:concat("AA", $i)),
"Quantity": fme:get-attribute(fn:concat("B", $i, "B")),
"Price": fme:get-attribute(fn:concat($i, "CC"))
}
]
Result:
e.g.
