How to create a single output record per unique label

USE CASE:

The below is an example of the scenario, each row is a separate row on an input source

Label 1, System B, Table 1, Other Data
Label 2, System A, Table 1, Other Data
Label 2, System A, Table 2, Other Data
Label 2, System B, Table 1, Other Data
Label 3, System A Table 1, Other Data

I want to create a single output record per unique Label (i.e. Label 1, Label 2, Label 3). Within that record I want to create a JSON message field that lists all the system and tables the label resides within.

I need to test if I have a change of label value since the last record processed. If the label is the same, I will concatenate the information to JSON output from last record.

RESOLUTION

There are a couple of ways to resolve this.

OPTION 1:

Try the Aggregate node, using the joinStrings() function

Example:
If you take your input and add some headers:

LabelNumber, System, Table, More
Label 1, System B, Table 1, Other Data
Label 2, System A, Table 1, Other Data
Label 2, System A, Table 2, Other Data
Label 2, System B, Table 1, Other Data
Label 3, System A, Table 1, Other Data

Then pipe that into an Aggregate node, and group by "LabelNumber", then in the "Operations" -> "Advanced" section then paste this into "ConfigureFields":

if node.properties.isSet('GroupBy', 'ls.brain.nodes.transform.groupBy'):
    outputs[0] += group.keyFields('GroupBy', 'ls.brain.nodes.transform.groupBy', 0)outputs[0]["LabelNumber"] = group.min(fields["LabelNumber"])out1.Tables = group.joinStrings(fields.Table, False, ", ", "Tables: ")
out1.Systems = group.joinStrings(fields.System, False, ", ", "Systems: ")
out1.More = group.joinStrings(fields.More, False, ", ", "More Info: ")

This will then give you the following, which you could then use in a Transform or some other node to make it into nice JSON:

LabelNumber	Tables	Systems	More
Label 1	Tables: Table 1	Systems: System B	More Info: Other Data
Label 2	Tables: Table 1, Table 2, Table 1	Systems: System A, System A, System B	More Info: Other Data, Other Data, Other Data
Label 3	Tables: Table 1	Systems: System A	More Info: Other Data

For more info on the joinStrings function see the online help: /docs/dist/help/Content/f-script/python/python-modules.htm#joinStri

OPTION 2:

First sort the data on the Label field, then a transform node with "GroupBy"=fields.Label, and "ImplicitWriteEvaluation"=Group End:

#ConfigureFieldsimport jsonout1.Label   = unicode
out1.Systems = unicode
out1.Tables  = unicode
out1.JSON    = unicode#ProcessRecords
if node.firstInGroup:
    properties = {}
    properties["Systems"] = []
    properties["Tables"]  = []if in1.System not in properties["Systems"]:
    properties["Systems"].append(in1.System)if in1.Table not in properties["Tables"]:
    properties["Tables"].append(in1.Table)if node.lastInGroup:
    properties["Systems"].sort()
    properties["Tables"].sort()    properties["Systems"] = ", ".join(properties["Systems"])
    properties["Tables"] = ", ".join(properties["Tables"])    out1.Label = in1.Label
    out1.Systems = properties["Systems"]
    out1.Tables = properties["Tables"]    out1.JSON = json.dumps(properties)

Articles in this section

Comments

Articles in this section

Related articles