LAE

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

Originally posted by: mwillett

Hi all,

I have some which essentially comprises 3 columns: (unique) customer ID, month/year and spend in that month/year. The data is compiled over 12 months and many customers have no spend in some months.

I want to create a a pivot on this with month running across the top (NamesField), customer ID running down the page (GroupBy) and spend as the data item (Data Field).

The issue I have is that, while I can sort the data by month before the Pivot node, as not all customers have data in each month, the output of the Pivot Node does not run in date order across the page, so I'd need to some work offline in Excel to copy and paste columns into the right order.

Is there anything that can be done about this in the Pivot node so these do come out in date order?

Mike

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

Originally posted by: mgajdosik

Hi,

I am not sure if it is possible to do that using only the node you mentioned (maybe it is), but what you can do is to create full set of all customer ids for all date periods upfront, having zero value inserted for the ones that should be empty.

Just use sort unique on customer ids, then next node sort unique on month/year. Then join these two nodes together setting left and right input key to 1. Every customer will have record for every month.

Then use lookup node to populate the records that have amount, the rest will be null. then you can use the node you wanted to.

Here is the LAE code, which you can paste into BRE:

Just change input and fieldnames in all nodes


node:Pivot__Data_To_Names_2
bretype:core::Pivot - Data To Names
editor:sortkey=4e99875d29b41888
input:@4ca9ea0e77f858a8/=Populate_Values.45781cad02e051b0
output:@4ca9ea197eed4a1a/=
editor:XY=620,250
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_3
bretype:::Agg
editor:shadow=4cd2e31a0deb376a
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg_3

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_2

node:Populate_Values
bretype:core::Join Left Inner
editor:Label=Populate Values
editor:sortkey=4e998745151f1c72
input:@45781ca80c2802d0/=Create_FUll_set.45781cad02e051b0
input:@45781ca971fe502f/=YOUR_INPUT.40fd2c742cab0bf0
editor:bend=1=240|350
editor:bend=1=420|350
output:@45781cad02e051b0/=
editor:XY=520,250
node:Sort
bretype:::Sort
editor:shadow=4578213227295e66
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Bypass_2
bretype:::Bypass
editor:shadow=4578213229d61611
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass_2

node:Sort_2
bretype:::Sort
editor:shadow=457821325111769a
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort_2

node:Join
bretype:::Join
editor:shadow=4578213269da7045
input:@40fd2c745b6d7704/=
input:@40fd2c74504921cd/=
output:@40fd2c7430f76546/=
end:Join

node:Bypass
bretype:::Bypass
editor:shadow=457821327950136b
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass

end:Populate_Values

node:Create_FUll_set
bretype:core::Join Inner
editor:Label=Create FUll set
editor:sortkey=4e9987264ef470cd
input:@45781ca80c2802d0/=Sort_Unique_on_Customer_ID.40fd2c746a2a3b47
input:@45781ca971fe502f/=Sort_Unique_On_MonthYear.40fd2c746a2a3b47
output:@45781cad02e051b0/=
prop:LeftInputKey=<<EOX
1
EOX
prop:Script=<<EOX

emit 1:CUSTOMER ID
emit 2:MonthYear

exclude referencedFields(2,{{^RightInputKey^}})

# Note: If you want to avoid collisions with fields on the inputs
# prefix the second emit statement with the keyword "default"
# or "override".


EOX
prop:SortLeftInput=true
prop:SortRightInput=true
editor:XY=400,250
node:Sort_2
bretype:::Sort
editor:shadow=457823031055565e
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort_2

node:Bypass_2
bretype:::Bypass
editor:shadow=457823031db44468
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass_2

node:Join
bretype:::Join
editor:shadow=457823031dc50173
input:@40fd2c745b6d7704/=
input:@40fd2c74504921cd/=
output:@40fd2c7430f76546/=
end:Join

node:Bypass
bretype:::Bypass
editor:shadow=4578230322105ec6
input:@45782010749f0c96/=
input:@457820124fef7658/=
output:@40fd2c7436717256/=
end:Bypass

node:Sort
bretype:::Sort
editor:shadow=457823032cda57c6
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

end:Create_FUll_set

node:Sort_Unique_On_MonthYear
bretype:core::Sort
editor:Label=Sort Unique On MonthYear
editor:sortkey=4e9987202d49528c
input:@40fd2c743ebf4304/=YOUR_INPUT.40fd2c742cab0bf0
output:@40fd2c746a2a3b47/=
prop:CompareOrderExpr=<<EOX
MonthYear

EOX
prop:Unique=true
editor:XY=290,260
end:Sort_Unique_On_MonthYear

node:Sort_Unique_on_Customer_ID
bretype:core::Sort
editor:Label=Sort Unique on Customer ID
editor:sortkey=4e9986fd701b4d62
input:@40fd2c743ebf4304/=YOUR_INPUT.40fd2c742cab0bf0
output:@40fd2c746a2a3b47/=
prop:CompareOrderExpr=<<EOX
Customer ID

EOX
prop:Unique=true
editor:XY=270,160
end:Sort_Unique_on_Customer_ID

node:YOUR_INPUT
bretype:core::BRD File
editor:Label=YOUR INPUT
editor:sortkey=4e9986f81e9a0410
output:@40fd2c742cab0bf0/=
editor:XY=180,260
end:YOUR_INPUT


Marek

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

Originally posted by: mwillett

Thanks Marek,

With a small bit of tweaking that worked well for my example.

There's a few things in your example that I will look at more closely in the next few days as some of them, I didn't realise you could do (as easily) these with Lavastorm.

Mike