LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Convert Rows to Columns

    Employee
    Posted 09-19-2018 08:24

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: JamieJ

    Hi,

    I am looking to dedupe on the number column, and move the values in the tag column up into one row.

    I have attached the node with the sample data, and below is a screenshot of the result I am looking for. Doesn't matter what the headers are labelled as.

    Untitled.jpg
    Attachments:
    BRE_TEMPLATE.brg


  • 2.  RE: Convert Rows to Columns

    Employee
    Posted 09-19-2018 12:53

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: gmullin

    Give this a go:

    node:Static_Data
bretype:core::Static Data
editor:sortkey=5ba1a3924b3709e9
output:@40fe6c55598828e5/=
prop:StaticData=<<EOX
Number:string, Tag:string
199825662,Fleet Sales
199825662,Irving Blending and Packaging
199825662,Irving Energy - Commercial
199825662,Wholesale and Specialty
199825675,Fleet Sales
199825675,Irving Blending and Packaging
199825675,Irving Energy - Commercial
EOX
editor:XY=130,90
end:Static_Data

node:Add_Tag_Id
bretype:core::Filter
editor:Label=Add Tag Id
editor:sortkey=5ba2a40d30895986
input:@40fd2c74167f1ca2/=Static_Data.40fe6c55598828e5
output:@40fd2c7420761db6/=
prop:Script=<<EOX
if firstExec then {
	_tagNames = 'Tag'
}
else {
	if _tagNames.strFind('Tag') == -1 then {
		_tagNames = _tagNames + ";" + 'Tag'
	}
}

_splitTags = _tagNames.split(";")

i = 0
_tag = 0

while i < _splitTags.len() {
	if 'Tag' == _splitTags[i] then 
		_tag = i + 1
		
	i = i + 1
}

emit *, "Tag " + str(_tag) as "TagID"

EOX
editor:XY=290,90
end:Add_Tag_Id

node:Pivot__Data_To_Names
bretype:core::Pivot - Data To Names
editor:sortkey=5ba2744a08912b98
input:@4ca9ea0e77f858a8/=Add_Tag_Id.40fd2c7420761db6
output:@4ca9ea197eed4a1a/=
prop:DataField=Tag
prop:GroupBy=<<EOX
Number
EOX
prop:NameSet=Union
prop:NamesField=TagID
prop:NullValueBehavior=Ignore
prop:OnDuplicate=Emit
prop:SortInput=true
prop:SubstituteInvalidChars=true
editor:XY=540,90
node:GroupByPath
bretype:::GroupByPath
editor:shadow=4cd2e31a25825f1b
input:@4ccfdae429e85cb5/=
output:@4ccfdae4585258de/=
node:DataToNamesWithGroupBy
bretype:::DataToNamesWithGroupBy
editor:shadow=4cd2e31a003467e9
input:@4cceefe2650d45e1/=
input:@4cceefe45c596005/=
output:@4cceeff7142d3549/=
end:DataToNamesWithGroupBy

node:Sort
bretype:::Sort
editor:shadow=4cd2e31a71bb6ba4
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Agg
bretype:::Agg
editor:shadow=4cd2e31a69415a14
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
output:@4cd003f34b1437c4/=
end:Agg

node:Bypass
bretype:::Bypass
editor:shadow=4cd2e31a078b0885
input:@4b467f7e129d45c1/=
input:@4b467f830ffe047b/=
output:@40fd2c7436717256/=
end:Bypass

node:Agg_2
bretype:::Agg
editor:shadow=4cd2e31a0deb376a
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg_2

end:GroupByPath

node:NoGroupByPath
bretype:::NoGroupByPath
editor:shadow=4cd2e31a55c8124a
input:@4ccfdae429e85cb5/=
output:@4ccfdae4585258de/=
node:DataToNamesNoGroupBy
bretype:::DataToNamesNoGroupBy
editor:shadow=4cd2e31a1d6263ef
input:@4cceefe2650d45e1/=
input:@4cceefe45c596005/=
output:@4cceeff7142d3549/=
end:DataToNamesNoGroupBy

node:Agg
bretype:::Agg
editor:shadow=4cd2e31a1bbb492b
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

end:NoGroupByPath

node:Bypass
bretype:::Bypass
editor:shadow=4cd2e31a1cd03eb3
input:@4ccfdb241245012a/=
input:@4ccfdb25174c47e7/=
output:@40fd2c7436717256/=
end:Bypass

end:Pivot__Data_To_Names


  • 3.  RE: Convert Rows to Columns

    Employee
    Posted 09-19-2018 13:56

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: JamieJ

    That's perfect! Thanks so much!
    Jamie