Multiple values into 1 target column
Moderators: chulett, rschirm, roy
Multiple values into 1 target column
hi all. i have a job that requires multiple source values to go into a single target field. the issue i have is that i have around 5 source fields per group that require entry in sequence into a traget field. how can this be made easy. any ideas most welcome. thanks in advance.
mark_e
Not sure what you mean by "require entry in sequence into a target field".
Do you need to concatenate them all into one large data element before sending it to the field? Or perhaps you need to turn one record into five, with each entry in your 'source group' creating a new record with a different value in that field?
Do you need to concatenate them all into one large data element before sending it to the field? Or perhaps you need to turn one record into five, with each entry in your 'source group' creating a new record with a different value in that field?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
hi. example: what i have is 5 source fields that need to go into 1 target field. the reason i said in sequence was that i need each of the 5 source fields on separate lines in the output (which is a single field). concatenation would still have them on a single row but i require each feild on separate rows. i hope this makes sense. thanks.
mark_e
First thing I'd suggest is you check out the Pivot stage, it does almost exactly what you need. Adding the INROWNUM bit to it might be interesting, however... or simple... don't know off the top of my head. Got no time to ponder something like that as I'm heading out the door when I hit Submit.
Read the pdf documentation for the stage and give it a whirl.
Read the pdf documentation for the stage and give it a whirl.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
thanks, that has worked when my source fields contain a single value. however some of my source fields contain multiple values which are separated with a '/'.
example
key sourcefield1 sourcefield2 sourcefield3
aaa aaa/nnn/mmm bbbbb ccccccc
bbb zzzzz hhhh/kkkk/jj nnnnnn
where this is the case the pivot won't work. how can i split these source fields that contain multivalues in order to populate my single target field.
thanks in advance.
example
key sourcefield1 sourcefield2 sourcefield3
aaa aaa/nnn/mmm bbbbb ccccccc
bbb zzzzz hhhh/kkkk/jj nnnnnn
where this is the case the pivot won't work. how can i split these source fields that contain multivalues in order to populate my single target field.
thanks in advance.
mark_e
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Hi. what if i have varying number of values that i need to split. i need every single value that is in the source field to go into the target field. so if i have say aaaa/bbbb/cccc/ddddd and i require all 4 values on separate rows in my target how exactly would i use the field function. won't the field function only extract the value i specify and not all the values. please note the source values per field vary from 1-20. thanks in advance.
mark_e
Well then, young Padawan, your job now becomes even tougher. You are going to have to spin up your own code to handle this. A couple of suggestions...
There are ways to count substrings in strings that you can find in the Help. You can use one of them to determine how many delimiters you have in your field and thus how many fields. You'll need to iterate through a process that pulls a single field until it processes the last one. Like a variable length arrary would need to be processed.
Check out this FAQ in the FAQ Forum. It discusses a technique you can adapt that will let you write out a variable number of records in a single record.
There are ways to count substrings in strings that you can find in the Help. You can use one of them to determine how many delimiters you have in your field and thus how many fields. You'll need to iterate through a process that pulls a single field until it processes the last one. Like a variable length arrary would need to be processed.
Check out this FAQ in the FAQ Forum. It discusses a technique you can adapt that will let you write out a variable number of records in a single record.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: