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:
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)
Comments
0 comments
Please sign in to leave a comment.