DSXchange

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.

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?

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.

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

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.

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.

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

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.

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.

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

The Devil is in the details, apparently...

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