Pivoting Question

[anudeepdasari001]

Input Data:
----------------
1000110011 101R4 K4 22,24,25,
1000151285 303R2 R4 9,10,
1000181003 303S2 R4 13,15,

Here is my Input Data, The last feild in there (22,24,25,) needs to go in as the seperate record, its not restricted to have two or three values in that feild,There might be more than one value


I Want this as an output,Whether this can be done with Looping in transformer or Pivoting, If yes, could someone explain the process in detail

Output:
_--------

1000110011 101R4 K4 22
1000110011 101R4 K4 24
1000110011 101R4 K4 25
1000151285 303R2 R4 9
1000151285 303R2 R4 10
1000181003 303S2 R4 13
1000181003 303S2 R4 15

Thanks

[chulett]

Since you don't know how many values may be in that last field then your best course of action would be to use 'transformer looping'. There you can get the count for each record and 'loop' that many times to parse those out and append them to your output. That or perhaps just 'convert' all those commas to spaces and you're already there. Depends on what your target is, a flat file? And your source - again a flat file?

Not sure what level of detail you are expecting from a forum post but for the looping approach, have you read the documentation for it? From what I recall it goes into quite a bit of detail on how to use it.

[anudeepdasari001]

What do you mean by parse those out and append them to your output?

And if you are converting the commas to the space, you still have those values in one feild, how to make them as a seperate record , My target is the Oracle database

[chulett]

Since you didn't specify, most of my comments assumed a sequential file output. Sorry but I also misread your output example, was thinking you just needed those values parsed out into separate fields in the same record, hence the 'append' comment.

So you actually need a pivot, specifically a horizontal pivot of columns to rows. And again, with no known maximum limit, we come back to transformer looping to parse those values out of the last field and create X number of records from them. The first three fields would be taken from the input and the fourth field taken from the last field based on the iteration count. I would think the Field() function would come in handy for that.

[anudeepdasari001]

I have tried with the looping in transformer, But some where missing the logic,I am confused how to create X number of fields from that last field, because there is no rule that it should cointain so many records.

[rkashyap]

What is your source?

[chulett]

So to know how many values are in the last field, count the delimiters into a stage variable. In your case, if there is always a trailing delimiter then count and subtract 1. Then loop while @Iteration is less than or equal to that number, using the Field function to pull out the "@Iteration" value from the delimited string each time.

[anudeepdasari001]

Just to give you a clear picture

My stage variables:

count(DSLink2.ABC,',') - 1 --> stage variable
@ITERATION <=StageVar -->loop condition
I am bit confuse on what should be inside loop body,

right now i am Using it as Field (DSLink2.ABC,',',@Iteration)
I am not sure whether i am understanding the field function correctly or not,, Does it require any change , Please help me out,, I am debugging with other code also, but if you can help me out that will be great

[anudeepdasari001]

Chulett,

This Worked for Three values
I didnt subtract the count by one,
beacuse when i do that its bringing only first two values but not third, so i am doing this



count(DSLink2.ABC,',') --> stage variable
@ITERATION <=StageVar -->loop condition
Field(DSLink2.ABC,',', @ITERATION )


This Work for three values in that field (eg: 13,14,15,), testing for another scenarios,Keep you posted

[chulett]

Cool. And yes, I got the count thing backwards. [sigh] Need to slow down a bit and not be posting when I should be working. [shhh, don't tell anyone] Normally, one would have a list like this: "12,15,33" so you would need to count the commas and then add one. But when you have a list like you do with a trailing delimiter: "12,15,33," you can just use the count as is.

Bottom line, you should be fine regardless of the number of delimited values in that field.

No need to quote everything all the time to reply, just use the Reply to topic link at the bottom of the page.

[anudeepdasari001]

Chulett,

Thanks, I am working on other values to check the code, Doing some testing