How can you pivot data (cross tab and transpose)

cross-tab and transpose data

 

1-2-3-4

a-b-c-d

 

1 a

2 b

3 c

5 d

 

Beginner Asked on July 24, 2019 in Tools & Utilities.
Add Comment
1 Answer(s)

Considering sources  as below.

Source 1: 

RE: How can you pivot data (cross tab and transpose)

Source 2: 

RE: How can you pivot data (cross tab and transpose)

And here col1, col2, col3, col4 in each source needs to be converted to rows.

Use the source 1 as source instance in the data flow four times and link them to a union transformation. In each source instance add an attribute to indicate the unique number to the column.

Source instances

RE: How can you pivot data (cross tab and transpose)

RE: How can you pivot data (cross tab and transpose)

Union transformation

RE: How can you pivot data (cross tab and transpose)

Output of union transform

RE: How can you pivot data (cross tab and transpose)

Similarly do this for source2 as well.

Output from the union transformations can be connected to the joiner transform and join based col_nm. The other column from the union should be added as attributes in the joiner.

RE: How can you pivot data (cross tab and transpose)

Attributes in joiner

RE: How can you pivot data (cross tab and transpose)

Output of joiner

RE: How can you pivot data (cross tab and transpose)

 

If the source is external then it is recommended to stage it first in the processing platform and use the staged data to do above.

Expert Answered on July 25, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.