Multiple values into 1 target column

Mark_E · Post by **Mark_E** » Sat Apr 09, 2005 9:32 am

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.

chulett · Post by **chulett** » Sat Apr 09, 2005 9:55 am

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?

Mark_E · Post by **Mark_E** » Sat Apr 09, 2005 10:04 am

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 · Post by **Mark_E** » Sat Apr 09, 2005 10:08 am

hi i forgot to mention they being grouped by a key:INROWNUM.

example:

key sourcefield1 sourcefield2 sourcefield3

aaa aaaaa bbbbb ccccc

the output i require is as follows:

key targetfield
aaa1 aaaaa
aaa2 bbbbb
aaa3 ccccc

thanks in advance

chulett · Post by **chulett** » Sat Apr 09, 2005 10:41 am

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.

Mark_E · Post by **Mark_E** » Sat Apr 09, 2005 12:06 pm

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Sat Apr 09, 2005 12:23 pm

Use Field(). Such as Field(InputField, '/', field_number)

Mark_E · Post by **Mark_E** » Sat Apr 09, 2005 1:34 pm

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.

chulett · Post by **chulett** » Sat Apr 09, 2005 3:58 pm

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.

ray.wurlod · Post by **ray.wurlod** » Sat Apr 09, 2005 4:56 pm

It's interesting to note how very different the final (?) specification was from the original specification!

chulett · Post by **chulett** » Sat Apr 09, 2005 5:16 pm

The Devil is in the details, apparently...

Mark_E · Post by **Mark_E** » Sun Apr 10, 2005 3:38 am

thanks for your suggestions. i shall check out the link and give that a go.