Twisting the data

[tracy]

I'm not sure what the terminology is for this situation, so I'm not sure how to search for a solution.

I've got a data file where a record looks like this:

09/09/2009|Tracy|Red|Green|Blue|Orange

I want to insert that into my database as 4 records like this:

09/09/2009 Tracy Red
09/09/2009 Tracy Green
09/09/2009 Tracy Blue
09/09/2009 Tracy Orange

In order to do this, I was going to process the file 4 times, pulling out each color individually. Then I'd do 4 loads.

Not a big deal when there's only 4. But my file actually has 30 and I didn't want to have such repetition.

I was hoping to be able to create a simple job and then create a loop to run it 30 times using a variable to pull different fields (for instance, use COLOR_X in my Router where I would substitute X with the Loop Number).

I couldn't get this to work. Is this possible. Or is there another method that I can look into?

[rameshrr3]

I see the Job Type is Server, but posting is in the Parallel Extender forum.

For your specific problem , you can use the Pivot Stage(Server), That should take care of the problem you describe.
Documentation on Pivot stage (server) is supplied in the Plug In Guides section.. Alternatively a search query on 'Pivot' or 'Horizontal Pivot'

Regards
Ramesh

[datability_user]

Hi Tracy;

you can try this, use the field function and stage variable like this:

1. stage variable value eg StgV = [repeated part]
eReplace( Field(YourCol,'|',1,2),'|',' ') = (09/09/2009 Tracy)
2. First column = StgV:' ':Field(YourCol,'|',3,1)
3. Second column = StgV:' ':Field(YourCol,'|',4,1)
.
.
.
.
.
30. Thirty column = StgV:' ':Field(YourCol,'|',33,1)

Hope this help

[chulett]

Hmmm... the Pivot stage is a much simpler solution. It exists to 'twist' columns into rows.

[datability_user]

Sorry,
i tried to help,
Graig, can i use the pivot stage, even the data to be splited contains in one column?

[chulett]

No worries. :D

To use the stage, you'd first need to parse the one column up into multiple columns and you could use the Field() function or the Column Import stage for that.

[tracy]

This worked! I should've been able to figure that out on my own. Thanks for answering my stupid question.