Pivoting Question
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
Pivoting Question
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
----------------
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
anudpETL
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
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
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
anudpETL
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona
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
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
anudpETL
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 58
- Joined: Mon Dec 14, 2015 3:16 pm
- Location: Arizona