Swapping Rows and Columns

[alexle]

Is there any trick for swapping rows and columns of a XLS datasource in Informatica? In other words, I have an XLS files that are row format and would like to turn those rows into Column Format using Informatica.

Original Format

# ProgID Desc ParentID
1 1000 TopLevel
2 1100 SecondLevel 1000
3 1110 ThirdLevel 1100

To this format

Level1 LV1_Desc Level2 LV2_Desc Level3 LV3_Desc
1000 TopLevel 1100 SecondLevel 1110 ThirdLevel

and so on...

The new table should keep the parent and child relationship.

Thanks.

 

[blom0344]

That would be a de-normalize step. This is pretty standard stuff ,but you will need to know in advance how many columns you will need to store the number of rows.

If you use a combination of expression and aggregator transformation you can first create outgoing ports for each row and than consolidate all data again through an aggregator. (using min or max function)

Ties Blom
Information analyst

 

[alexle]

Thank you for responding. I know for a fact that I need 5 columns. However, the present datasource doesn't lay out neatly for me to insert the rows into the columns. In other words, the current datasource is in the tree format.

Would the normalizer expression help?