AGGREGATE component: how to perform "concatenate"

achan

• January 06, 2009 00:00

• Answered

Follow

Hi,

How do I concatenate all the values from an input field to an output field for an Aggregate component?

for example, my input source looks like this:

key, partial_name
1, albert
1, daniel
1, martin
2, diane
2, anne
2, clayton

and i want an output that look like this:

key, full_name
1, albert daniel martin
2, diane anne clayton


so my aggregateKey="key", but there is currently no aggregate functions to achieve my output full_name (which is combining all the partial_name for each group)...

is there a workaround or fix for this situation?

Thanks,
albert

• 

  hwhwhw

  • January 07, 2009 06:19

  0

  Comment actions Permalink

  I have the same similar problems,
  for example, my input source looks like this:
  c1,c2,c3
  1,100,110
  1,200,220
  2,300,330
  2,400,440
  and i want an output that look like this:
  c1, c2_add_c3
  1,420
  2,840
  
  my aggregateKey="c1", how to achieve c2 field values and field values the sum of C3 and output the result
• 

  avackova

  • January 07, 2009 07:58

  0

  Comment actions Permalink

  Hello,
  Denormalizer component does it.
• 

  hwhwhw

  • January 07, 2009 08:53

  0

  Comment actions Permalink

  I would like to achieve through AGGREGATE component division, aggregation function, a number of fields and logic operations,The logic operation of the how to achieve
• 

  achan

  • January 07, 2009 23:22

  0

  Comment actions Permalink

  Thanks, Agata!
  
  al
• 

  hwhwhw

  • January 08, 2009 01:03

  0

  Comment actions Permalink

  AGGREGATE Component how to achieve the following SQL statement function?
  --------------------------------------------------------------------
  select max(c2)+max(c3) from t_1 where group by c1;
  
  for example, my input source looks like this:
  c1,c2,c3
  1,100,110
  1,200,220
  2,300,330
  2,400,440
  
  and i want an output that look like this:
  c1, c2_add_c3
  1,420
  2,840
  ---------------------------------------
  select sum(c2)+sum(c3) from t_1 where group by c1;
  
  and i want an output that look like this:
  c1,c2_add_c3
  1,630
  2,1470
• 

  avackova

  • January 08, 2009 09:53

  0

  Comment actions Permalink

  You can easily do it by Denermalizer:
  

  
  int maxC2;
  int maxC3;
  
  function clean(){
  maxC2=0;
  maxC3=0;
  }
  
  function append(){
    if (maxC2 < $c2) maxC2=$c2;
    if (maxC3 < $c3) maxC3=$c3;
  }
  
  function transform(){
    $c1:= $c1;
    $c2:=maxC2;
    $c3:=maxC3;
  }
  

• 

  hwhwhw

  • January 09, 2009 04:50

  0

  Comment actions Permalink

  Thank you, indeed can be achieved,
  but I think this function should be AGGREGATE component part of,
  please consider.:)
• 

  anweston

  • January 21, 2009 23:25

  0

  Comment actions Permalink

  I am going to use the DENORMALIZE component that was suggested to Albert, but it would be nice for us if the AGGREGATE component did do this sort of concatentate (string concatenation instead of the number addition of sum), because most of our fields will need functions identical to those offered by AGGREGATE. For example, we have something like:
  
  key, buyer, amt_paid, paid_date
  1, albert, 22.00, 12/31/2008
  1, daniel, 23.00, 12/01/2008
  1, martin, 40.00, 12/30/2008
  2, diane, 55.00, 11/28/2008
  2, anne, 20.00, 12/01/2008
  2, clayton, 15.00, 12/15/2008
  
  What I would want to do is sort by key, then date descending, and produce a mapping like:
  
  $key:=$key;$buyers:=concatenate($buyer,~);$amt_paid:=sum($amt_paid);$final_paid_date:=first($paid_date)
  
  which would get me the following results (assuming I do this right manually!):
  
  key,buyers,amt_paid,final_paid_date
  1,albert~daniel~martin,85.00,12/31/2008
  2,diane~anne~clayton,90.00,12/15/2008
  
  
  If I use the DENORMALIZER component, it sounds like I have to reproduce the same functions as AGGREGATE component (Maybe there's a way to expose the AGGREGATE component functions in the DENOMRALIZER component?). It's not hard, but I wonder if this DENORMALIZER will have as good performance as the Clover AGGREGATE component. On the other hand, 'concatenate' not be a common case for other users, I'm sure you're flooded with request to add very custom functionality to Clover's core, and it sounds like this is one of the reasons the DENORMALIZER component exists (to handle custom cases).
  
  Any thoughts?
  
  Anna :-)

Please sign in to leave a comment.