Merge Join or Union All

Hi,

I am trying to normalize data using the unpivot transform. I have to unpivot using more than one key so I have a multicast feeding into two unpivot transforms then into a sort transform. This is where my problem starts - I have tried using a Merge Join (inner Join) transform but dont get the expected result.

My original data looks like this:

Pk_ID

Choice1

Choice2

Feedback1

Feedback2

10

a

b

x

y

After the mulitcast - unpivot - Merge Join, the expected result is: (pk_newID is an identity)

Pk_newID

fk_ID

Choice

Feedback

563

10

a

x

564

10

b

y

However with a Merge-Join (inner join on pk_ID) I get

Pk_newID

fk_ID

Choice

Feedback

563

10

a

x

564

10

a

y

565

10

b

x

566

10

b

y

Is the Merge Join transform not the right choice?

Thanks

Which columns are you using for the join in the Merge join component...if you use only fk_id in your join the output, even when the output you get is not the expected; it is perfectly valid.

It seems like you need something else for your join...or may be the unpivot is not an option in this case

Rafael Salas

|||I think you are going to perhaps have to do some conditional logic as was done in your other thread. Take a look at it from a different perspective as I did in my example and perhaps you can get to where you need to be...