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
+45Hello @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
+28How 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)?
+6If 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 approach Do this more manually since there are only a total of 7 values - one record becomes 7 Branch the feature data flow 7 ways, and then have AttributeManagers that just keep their records, AttributeManager1 creates record number 1 and maps name 1 to name, and a parallel AttributeManager2 creates record number 2 and maps name 2 to name etc.Last approach is to just leave everything on one record and create a new field called TotalQuantity = @Value(quantity1) + @Value(quantity2) etcHello @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
+45
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>
[Addition] 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:
[
{
"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")
}
]
[Update] This expression is also available.
[
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>
[Addition] 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:
[
{
"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")
}
]
[Update] This expression is also available.
[
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
