DSXchange

Hi All
i have an input flat file which contain data in this format

ID CODE D1 D2 D3 D4
123 1 a b c x
123 2 A B C Y
456 1 e f g h
456 2 E F G H

i need in out out this format

ID CODE1 D1 D2 D3 D4 CODE2 D1_2 D2_2 D3_2 D4_2
123 1 a b c x 2 A B C Y
456 1 e f g h 2 E F G H


can any one tell what stage can i use or what transformation i use in TX

Thanx

Do you mean PX or TX? You've posted in the parallel (PX) forum, but asked a question about TX (Mercator). The answer will be different in each case.

In a parallel job I'd use a Transformer constrained to output only when CODE is 2. I'd store the code 1 column values in stage variables conditionally updated only when CODE is 1, and use these in the output column derivations.

This approach requires that the input is sorted by ID then by CODE and that there is a guaranteed code 2 for each code 1. Without that, the orphan code 1 rows will be discarded.

If you need a TX solution, please post the question on the TX forum.

Hi

What you are trying is known as 'Vertical Pivot' in PX, search on the keyword and you will get your answer.

Thanks
Sam