Pivot Stage

patonp · Post by **patonp** » Mon Sep 21, 2009 8:20 am

In a sample set of data, the format of my input file appears as follows:

CUST ID, HOME_PHONE, BUS_PHONE, ALT_PHONE

I'd like to normalize the data so that the output appears as follows:

CUST_ID, PHONE_NUM, PHONE_TYPE

The PHONE_TYPE column would reflect whether the value in PHONE_NUM was sourced from the HOME, BUS or ALT column.

Is this possible using the Pivot stage?

Thanks!

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Sep 21, 2009 9:09 am

You can. But it is difficult to trace the source of phone.

It may be useful to have 3 links from a transformer with each representing one phone type and funneling them together.

chulett · Post by **chulett** » Mon Sep 21, 2009 9:13 am

Simple enough to manually add the 'phone_type' columns (x3) to the data before the pivot and then drop any pivoted rows where the number was null post-pivot.

patonp · Post by **patonp** » Mon Sep 21, 2009 9:47 am

Thanks Craig - works great!