DSXchange

I need to do the following data transformation example:

Input file:
ID, Line1, Line2, Line3
1, 10, 20, 30
2, 100, 200, 300

Required output table structure:
ID, Line, Value
1, Line1, 10
1, Line2, 20
1, Line3, 30
2, Line1, 100
2, Line2, 200
2, Line3, 300

Can I Use the Transformer loop to accomplish this, and in case of yes, can you please mention a brief example on how to do that and kindly mention if it's gonna have a performance drawbacks if exist

Thank you in advance for your help
Soha

Or to remove the if then else from the loop variable, you could have 2 stage variables,
sv1: containing the input header names - ie a literal string 'Line1|Line2|Line3'
sv2: containing the row input values (as suggested by bhasds)

You can then replace the loop variable
with
lv1: sv1['|',@ITERATION,1]
lv2: sv2['|',@ITERATION,1]

and output them accordingly.

Thank you so much for your reply bhasds, i have tried that logic, but it didn't gave me the required results..It gave me the same result I have already got using the transformer loop without the column names converted to rows with each value.. your solution just added another columns with the values concatenated, which a thing I don't need in my output.

please note the following too:
1- I need to include the 'Line' column in my output as shown in my example
2- The input file doesn't contain a fixed number of columns, i mean it could be three columns or more! :S

so I want to accomplish the following:
1- I want to handle the issue of the dynamic number of columns first
2- I need to convert them into rows with thier values

Any suggestions?

I just want to point out that since this is a horizontal pivot, the Pivot stage can handle this just fine. Yes, even with the column name column thrown in. All that needs is for you to add three new columns into the flow and 'hard code' the column names in them, they can then be pivoted in pairs with each value. There are posts here explaining the technique that a search would turn up. FYI.

Edited to add: This advice was posted before the clarification was made as to the number of columns. IMHO parallel looping is the better solution in this case but the Pivot stage can still handle this as long as you know the maximum number of columns and are willing to filter out any "empties" post-pivot.

sohasaid wrote:2- The input file doesn't contain a fixed number of columns, i mean it could be three columns or more!

Any suggestions?

As a suggestion, that would have been an excellent bit of information to include in your initial post.

Thank you chulett for your replies; however, I couldn't read any of your suggestions it's hidden as a Premium content

how can I read them?!

sohasaid wrote:1- I need to include the 'Line' column in my output as shown in my example
2- The input file doesn't contain a fixed number of columns, i mean it could be three columns or more! :S

Ok you will need to do the following:
1. Have a stage variable which determines the number of columns that you have.
2. Create/Read in your column headings into a single string with some form of delimiter, eg '|'.
3. Add the above string of header values into a stage variable (sv1).
4. Create/Read in your column values into a single string with some form of delimiter eg '|'.

5. In the loop: Loop While @ITERATION <= number of columns
6. Set up 2 loop variables:
LoopVar1: Sv1['|',@ITERATION,1]
LoopVar2:InputCol.ValueString['|',@ITERATION,1]

7. Set your output columns accordingly
ID: InputCol.ID
Line: LoopVar1
Value: LoopVar2

Enjoy.

sohasaid wrote:Thank you chulett for your replies; however, I couldn't read any of your suggestions it's hidden as a Premium content

how can I read them?!

That one is simple... renew your Premium Membership.