Melt Data from Wide format to Long format

yichuansancun

• January 31, 2015 00:00

• Answered

Follow

Hi is there a component in Clover can achieve the following result:

Input Metadata
EmployeeID
AddressType
EffectiveDate
Country
Address1
Address2
Address3
Address4
City
State
County

Output Metadata
EmployeeID
AddressType
EffectiveDate
Country
Address Component
Address Value

The value for "Address Component" of the output metadata should the "Address 1", "Address 2", etc depending on the name of input metadata. And the value for "Address Value" of the output metadata should be the value of "Address 1", "Address 2", etc.

I know this can be achieved by writing code in Reformater, but wondering if there is another component I can use that is ready to go.

• 

  admin

  • February 02, 2015 12:58

  0

  Comment actions Permalink

  Hi yichuansancun,
  
  If I understand you correctly there should be N output records generated from one single record, right?
  
  Then you can use Normalizer component. Just define how many output records will be created and define output record. You can access fields dynamically using function getStringValue(record, name). So just compose name as "Address"+index.
  
  I hope this helps.
• 

  yichuansancun

  • February 02, 2015 15:22

  0

  Comment actions Permalink

  I did look into the Normalizer component, but couldn't figure how to use it. Is there a tutorial video that I can watch? Or can you create a simple graph that uses this component?
• 

  imriskal

  • February 03, 2015 11:17

  0

  Comment actions Permalink

  Here is a very simple Normalizer example:normalizer.zip
  Regards,

  • normalizer.zip
• 

  yichuansancun

  • February 08, 2015 22:30

  0

  Comment actions Permalink

  Thanks for posting the sample graph. In your sample graph, the id 1 and 2 are being generated to indicate phone 1 and phone 2. However, what should I do if I want to preserve the column headers of phone 1 and phone 2?
  
  For example, what if phone 1 column is called "City" and phone 2 column is called "State". In this case, after the normalization, I need to know which row is City and which row is State. 1 and 2 will not be helpful in this case.
  
  Thanks,
  Perri
• 

  imriskal

  • February 16, 2015 12:52

  0

  Comment actions Permalink

  IDs 1 and 2 do not indicate phone 1 and phone 2, they identify the person.
  
  I have modified the original graph. I added a new field called source and this field contains name of the original field. Is this solution sufficient?normalizer_modified.grf

  • normalizer_modified.grf

Please sign in to leave a comment.