How to create single value record from multivalue CSV

smof

• July 09, 2008 00:00

• Answered

Follow

I have a CSV file with the following style of output:

name, pet
-----------
simon, dog
simon, cat
dave, hamster
dave, pig
mike, mouse

I have used a REFORMAT to create a list of just names, then DEDUP
to create a list of distinct names. I then attempted to compare the master
and slave feeds from this to create the following....

name, pet
------------
simon, "dog,cat"
dave, "hamster,pig"
mike, "mouse"

...but so far I can't do it.

Has anyone seen a solution to this before?? Is there any built in transforms that I can use to do this or attempt to create a customer jar?

I'm really stuck. Any ideas welcome!!

TIA.

• 

  avackova

  • July 10, 2008 07:15

  0

  Comment actions Permalink

  Hi, it's really easy :wink: : read the data, sort them by name and then use the Denormalizer component.
• 

  smof

  • July 10, 2008 13:43

  0

  Comment actions Permalink

  Doesn't that mean my TL transform has to contain a hardcoded listing of each user_name?
• 

  avackova

  • July 11, 2008 07:08

  0

  Comment actions Permalink

  It hasn't. You need only write TL function addInputRecord() where you remember a new pet (eg. add it to list) and getOutputRecord() function, which fills output field with all stored pets; in clean() function you can clear the list.
• 

  smof

  • July 11, 2008 12:24

  0

  Comment actions Permalink

  Sorry! I'm really confused. This is what I've tried and it only picks up the last pet the individual has for all names.
  
  How can I concatenate the pets together or, at least get another field with the second and third pets?
  
  <Graph>
  <Global>
  <Metadata>
  <Metadata>
  </Global>
  <Phase>
  <Node>
  
  <Node>
  
  <Node>
  
  <attr>//#TL
  string name;
  string pet;
  string all_pets;
  
  function addInputRecord() {
  name = $name;
  pet = $pet;
  }
  
  function getOutputRecord() {
  $name :=name;
  $all_pets := pet;
  
  
  }
  </attr>
  </Node>
  
  <Node>
  
  
  <Edge>
  
  <Edge>
  
  <Edge>
  
  </Phase>
  </Graph>
• 

  avackova

  • July 11, 2008 12:33

  0

  Comment actions Permalink

   <attr>//#TL
  string name;
  string pet;
  string all_pets;
  
  function addInputRecord() {
  name = name;
  pet = pet + ',' + $pet;
  }
  
  function getOutputRecord() {
  $name :=name;
  $all_pets := pet;
  pet="";
  }
  
  
  </attr> 

• 

  smof

  • August 14, 2008 17:01

  0

  Comment actions Permalink

  Hi there
  
  I've tried the above, but I end up getting a none unique list of pets per each user. So for the last user in the sorted file, their list of pets will contain all pets from all the above users, not just the pets that belong to them.
  
  How can I clear down the list before getObjectRecord processes each name?
• 

  smof

  • August 14, 2008 17:15

  0

  Comment actions Permalink

  ignore that!
  
  I've sorted it.
  
  Thanks

Please sign in to leave a comment.