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.  Pivot Table Node Issue

    Employee
    Posted 07-13-2016 06:34

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

    Originally posted by: ahines

    Hello, Everyone!

    To begin, I am certain that this is something I'm just not configuring correctly on my end, as I am not getting an error or a timeout, but I've never had a node take this long to run. Essentially, I have a pivot table node attached to the output of a filter, with approximately 140k rows of data, and 17 columns. All I want to do is pivot the first column (store numbers), to show a sum of the Write Offs associated to that store. For whatever reason, the nod has been running for 17m and hasn't errored or timed out, and the progress bar is 2/3 of the way filled and hasn't moved. It's done this half a dozen times. My configuration is below as well as a sample data set. What am I doing wrong? I thought it may have to do with the AggregateOverField and the GroupForColumn being the same, but I cannot find anything about the AOF field to tell me otherwise.

    node:Pivot_Table
bretype:lal1::Pivot Table
editor:sortkey=57852cab516147a7
input:@4dbb062269be5554/=Split_By_Pattern.40fd2c7420761db6
output:@4dbb0622243439a0/=
prop:AggregateOverField='Write Off Amount'
prop:AggregationType=Sum
prop:GroupForColumn='Write Off Amount'
prop:GroupForRow='Cost Center'
editor:XY=1160,140
node:Pivot__Data_To_Names
bretype:::Pivot - Data To Names
editor:shadow=5178fbff00f243c8
input:@4ca9ea0e77f858a8/=
output:@4ca9ea197eed4a1a/=
node:GroupByPath
bretype:::GroupByPath
editor:shadow=5178fbff67c640bd
input:@4ccfdae429e85cb5/=
output:@4ccfdae4585258de/=
node:DataToNamesWithGroupBy
bretype:::DataToNamesWithGroupBy
editor:shadow=5178fbff07b61b1e
input:@4cceefe2650d45e1/=
input:@4cceefe45c596005/=
output:@4cceeff7142d3549/=
end:DataToNamesWithGroupBy

node:Sort
bretype:::Sort
editor:shadow=5178fbff4e1d3af2
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

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

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

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

end:GroupByPath

node:NoGroupByPath
bretype:::NoGroupByPath
editor:shadow=5178fbff6d492169
input:@4ccfdae429e85cb5/=
output:@4ccfdae4585258de/=
node:DataToNamesNoGroupBy
bretype:::DataToNamesNoGroupBy
editor:shadow=5178fbff79cc514a
input:@4cceefe2650d45e1/=
input:@4cceefe45c596005/=
output:@4cceeff7142d3549/=
end:DataToNamesNoGroupBy

node:Agg
bretype:::Agg
editor:shadow=5178fbff2c0b2e88
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

end:NoGroupByPath

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

end:Pivot__Data_To_Names

node:Join_Left_Inner
bretype:::Join Left Inner
editor:shadow=5178fbff0c954ea7
input:@45781ca80c2802d0/=
input:@45781ca971fe502f/=
output:@45781cad02e051b0/=
node:Sort
bretype:::Sort
editor:shadow=5178fbff1f306e4f
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

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

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

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

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

end:Join_Left_Inner

node:Take_out_Total_row
bretype:::Take out 'Total' row
editor:shadow=5178fbff77aa1fd7
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
output:@456df11556bd6bcf/=
end:Take_out_Total_row

node:Cat_Total_row_on_at_end
bretype:::Cat 'Total' row on at end
editor:shadow=5178fbff78137301
input:@40fd2c7476b11c42/=
input:@5149d0c656d97325/=
output:@40fd2c74676e03c3/=
end:Cat_Total_row_on_at_end

node:Calc_Count_CountNulls_Sum
bretype:::Calc: Count, CountNulls, Sum
editor:shadow=5178fbff5583598e
input:@515171cc1aef070a/=
output:@515171cc0ba76831/=
output:@515171cc03df0ba3/=
node:Bypass
bretype:::Bypass
editor:shadow=5178fbff1a6a41c8
input:@5152d1d76f2d6732/=
input:@5152d1d91b6253c5/=
input:@5152d1db6eb148e3/=
output:@40fd2c7436717256/=
end:Bypass

node:Cat
bretype:::Cat
editor:shadow=5178fbff5b0a39a4
input:@40fd2c7476b11c42/=
input:@5152d2e5007e5e8b/=
output:@40fd2c74676e03c3/=
end:Cat

node:Count_2
bretype:::Count
editor:shadow=5178fbff556c7741
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Count_2

node:Count_Nulls_2
bretype:::Count Nulls
editor:shadow=5178fbff1e697e69
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Count_Nulls_2

node:Sum_2
bretype:::Sum
editor:shadow=5178fbff2bb112ef
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Sum_2

node:Sort
bretype:::Sort
editor:shadow=5178fbff4b26671c
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Total_for_Columns_3
bretype:::Total for Columns
editor:shadow=5178fbff5a122e9a
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Total_for_Columns_3

node:Total_for_Rows
bretype:::Total for Rows
editor:shadow=5178fbff62f9416a
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Total_for_Rows

end:Calc_Count_CountNulls_Sum

node:Bypass
bretype:::Bypass
editor:shadow=5178fbff3f1917f4
input:@51530345331e5cd9/=
input:@515304817d541a6e/=
input:@515328e24c3a371b/=
output:@40fd2c7436717256/=
end:Bypass

node:Bypass_3
bretype:::Bypass
editor:shadow=5178fbff00db7032
input:@5153049945407b2a/=
input:@5153049b64d64f85/=
input:@515328e562cc532f/=
output:@40fd2c7436717256/=
end:Bypass_3

node:Sort
bretype:::Sort
editor:shadow=5178fbff56b72d7c
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Calc_Min_Max
bretype:::Calc: Min, Max
editor:shadow=5178fbff52604f8b
input:@51530c9d3d13785e/=
output:@51530c9d111b697a/=
output:@51530c9d31497d08/=
node:Cat
bretype:::Cat
editor:shadow=5178fbff1e3502a3
input:@40fd2c7476b11c42/=
input:@515300af2d89609b/=
output:@40fd2c74676e03c3/=
end:Cat

node:Agg
bretype:::Agg
editor:shadow=5178fbff3b461a08
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

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

node:Sort
bretype:::Sort
editor:shadow=5178fbff574e333b
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Agg_3
bretype:::Agg
editor:shadow=5178fbff4a3a6ddb
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg_3

end:Calc_Min_Max

node:Bypass_2
bretype:::Bypass
editor:shadow=5178fbff4e4a7b77
input:@51530f8902357a86/=
input:@51530f92533611a8/=
input:@5153291062cb5fd0/=
output:@40fd2c7436717256/=
end:Bypass_2

node:Min_of_Mins_or_Max_of_Maxs
bretype:::Min of Mins or Max of Maxs
editor:shadow=5178fbff372d6e47
input:@515328020c3d170b/=
output:@515328026510601e/=
node:Exclude_newly_created_Total_row_from_Min_of_mins_eval
bretype:::Exclude newly created Total row from Min of mins eval
editor:shadow=5178fbff2e7810da
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Exclude_newly_created_Total_row_from_Min_of_mins_eval

node:Min_of_Mins_or_Max_of_Maxs
bretype:::Min of Mins or Max of Maxs
editor:shadow=5178fbff46827e0f
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Min_of_Mins_or_Max_of_Maxs

node:Bypass
bretype:::Bypass
editor:shadow=5178fbff410e218b
input:@516e922e7bcd2123/=
input:@516e925823001dfb/=
output:@40fd2c7436717256/=
end:Bypass

end:Min_of_Mins_or_Max_of_Maxs

node:Calc_Mean
bretype:::Calc: Mean
editor:shadow=5178fbff10522d35
input:@51543c6f7ad46391/=
output:@51543c6f7e2a0221/=
output:@51543c6f446176e2/=
node:Cat
bretype:::Cat
editor:shadow=5178fbff0d163341
input:@40fd2c7476b11c42/=
input:@51543c5b259b1f93/=
output:@40fd2c74676e03c3/=
end:Cat

node:Sort
bretype:::Sort
editor:shadow=5178fbff730a51c6
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort

node:Cat_3
bretype:::Cat
editor:shadow=5178fbff7c7c0405
input:@40fd2c7476b11c42/=
input:@51546fc153222b3d/=
output:@40fd2c74676e03c3/=
end:Cat_3

node:Sort_4
bretype:::Sort
editor:shadow=5178fbff011b0eba
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort_4

node:Agg
bretype:::Agg
editor:shadow=5178fbff57666838
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg

node:Sort_3
bretype:::Sort
editor:shadow=5178fbff76f3421c
input:@40fd2c743ebf4304/=
output:@40fd2c746a2a3b47/=
end:Sort_3

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

node:Overall_Mean_2
bretype:::Overall Mean
editor:shadow=5178fbff4d2d1e6b
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Overall_Mean_2

node:Agg_3
bretype:::Agg
editor:shadow=5178fbff67272cfe
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Agg_3

end:Calc_Mean

node:Total_of_Totals_2
bretype:::Total of Totals
editor:shadow=5178fbff3b04733f
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
end:Total_of_Totals_2

node:Error_or_Ignore_NULL
bretype:::Error or Ignore NULL
editor:shadow=5178fbff60d65dab
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
end:Error_or_Ignore_NULL

node:Validate_Fields_Given
bretype:::Validate Fields Given
editor:shadow=51a7590d76416861
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
end:Validate_Fields_Given

node:Bypass_5
bretype:::Bypass
editor:shadow=5178fbff3d0e692f
input:@516d0d3070964340/=
input:@516d0d337e743cae/=
output:@40fd2c7436717256/=
end:Bypass_5

node:Change_Metadata
bretype:::Change Metadata
editor:shadow=51a754192caa3483
input:@516511412c32051f/=
input:@5162dc5f486743e5/=
output:@51778acf012c236e/=
output:@5163cf7b3f3e218f/=
end:Change_Metadata

node:Change_Metadata_3
bretype:::Change Metadata
editor:shadow=51a7619d6a3f4ceb
input:@516511412c32051f/=
input:@5162dc5f486743e5/=
output:@51778acf012c236e/=
output:@5163cf7b3f3e218f/=
end:Change_Metadata_3

node:Rename_internal_name_to_external_name
bretype:::Rename internal name to external name
editor:shadow=51a761227ad665f9
input:@40fd2c74167f1ca2/=
output:@40fd2c7420761db6/=
end:Rename_internal_name_to_external_name

node:Static_Data
bretype:::Static Data
editor:shadow=51a7619a0dc84186
output:@40fe6c55598828e5/=
end:Static_Data

node:Rename_Fields
bretype:::Rename Fields
editor:shadow=51a7876e1f236640
input:@4665b06168a410a5/=
output:@4665b05e24fd0056/=
end:Rename_Fields

end:Pivot_Table

    Sample Data:

    Unit Number:INT Write Off:DOUBLE
    1234 1.69
    1234 -3.0
    1234 179.35
    1234 -4053
    1235 -2
    1235 2000
    1235 120
    1235 500


  • 2.  RE: Pivot Table Node Issue

    Employee
    Posted 07-13-2016 15:34

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

    Originally posted by: ahines

    Update: I reversed the GroupForRow and GroupForColumn entries just to see if I was confusing the system in some way. 50 minutes later I got a 40k row table of what I expected- columns for every unique dollar value, and a row for every unit. I've now put the settings back to where they make sense for my original request and am going to just let it sit and think. Hopefully someone has an answer as to what is bogging this down. I expect a 2 column output of all the unique unit numbers and a sum of the Write Off data next to it. Bonus points if it comes out formatted correctly!


  • 3.  RE: Pivot Table Node Issue

    Employee
    Posted 07-13-2016 16:19

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

    Originally posted by: stonysmith

    There's no need to use the Pivot node.

    Setup an AggEx node

    GroupBy='Unit Number'

    Script code:
    t=groupSum('Write Off')
emit referencedFields(1,{{^GroupBy^}})
emit t as WriteOff
where lastInGroup


  • 4.  RE: Pivot Table Node Issue

    Employee
    Posted 07-14-2016 04:40

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

    Originally posted by: ahines

    Thanks, Stony- I really appreciate it. that worked like a charm. It may be a stupid question, but any idea why the pivot table node wouldn't perform the same way?


  • 5.  RE: Pivot Table Node Issue

    Employee
    Posted 07-14-2016 06:55

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

    Originally posted by: stonysmith

    I would need to see it running (with real data) to try to figure out what was taking so long.
    If you right-click and OPEN the Pivot node you'll see that there is a good bit of stuff going on in there.
    It's hard for me to determine what might have taken so long. You might try running it once and watch how long it takes to run each individual node inside.

    My suspicion is the DataToNames node. If that node is trying to create a large number of columns, then that could be the explanation for the slowness. This slowdown could happen even if some of the columns later dropped.

    Again, I'd have to watch it in operation. AGG is your friend. <grin>