Repetition of data

[Mark_E]

hi all. i posted a topic last week with regards to a source colmun which is delimited that i require to seperate and create multiple rows in the target colmumn. i.e aaa/ddd/ccc. this was acheived by the char(013):char(010) this worked fine. thanks for all your help.

the problem i have now is that i have an id field which is associated with the delimited column that i require generating x amount of times in accordance with the delimited rows.

ie.

source data

id field1

1 aa/ddd/ccc


desired result

id field1

1 aa
1 ddd
1 ccc

how can i create multiple values of the id column. as a test i have hardcoded the value 1 but it only occurs the once in the target table.

thanks in advance

[ArndW]

Hello Mark_E,

if I understand your requiement correctly, this is a perfect example for the use of the PIVOT stage; had you looked into that?

[Mark_E]

hi, the Pivot stage works fine if i have single values in my source column and not delimited data that needs to go into target as separate rows but this is what i have. this can be fixed quite easily.

what i require is all additional fields from the source ie 'id field' which is a single value to be repeated for x amount of rows of the delimited field.

all suggestions most welcome.

currently the derivations are as follows.

output colum - Names
derivation
Ereplace(DSLink13.names, "/", Char(013) : Char(010), -1, 0)

Output column - Id
derivation - Id

but it only populates the last row for id. all others are blank for the id output column.

thanks

[ArndW]

Mark_E,

how about reading it in as 2 columns, doing a transform which splits your data column into 3 columns and then doing the pivot on the 3 columns?

or just modify your ereplace to read

Code: Select all

Ereplace(DSLink13.names, "/", Char(013) : Char(010):DSLink13.id, -1, 0)

and then ignore the last line (which would just contain an id but no data.

[Mark_E]

hi, thanks for your response.

i tried the modified ereplace but the thing is i need 2 columns in my output. at the moment i have 2 source columns of ( name, id) and i require 2 target column populated (name, id). the modified ereplace placed them all into a single column which isn't what i require.

at the moment i am reading in the 2 columns doing my original ereplace to format the output (for names) and to split the embedded values to create multiple rows and a straight mapping of id to the id target column.

id is causing the problems. Ex, if i have 4 rows for the names output. i only have the last row of column 'id' populated.

thanks

[ArndW]

Mark_E,

look at my ereplace suggestion a bit more closely; and add a column separator character (don't know what you used...).

[Mark_E]

hi, thanks for all you're assistance. i used the following code in the names derivation:

Ereplace(DSLink13.names, "/", Char(013) : Char(010):DSLink13.id, -1, 0)

but it placed it all into a single column. how would i add a column seperator. not quite sure how to go about that. don't i need to place a derivation of some sort in the id output column.

thanks

[ArndW]

Mark,

what is your column separator in your text file? If it is a comma, then make the ereplace read:

Code: Select all

Ereplace(DSLink13.names, "/", Char(013) : Char(010):DSLink13.id, -1, 0):','

[Mark_E]

hi,

for the id column there is no separator it is just a static value which i need to repeat. the names column has the separator but i have that working. it's the static value of id which i need to create duplicates of.

ex: SOURCEFILE

id names
1 aaa/bbb/ccc



what i require is the following:

names id
aaa 1
bbb 1
ccc 1


what i am getting is:

names id
aaa
bbb
ccc 1

[ArndW]

Sigh.... I confused....

if your sequential file looks like "1 aaa/bbb/ccc" as you stated, then your separator (which you must define in the sequential file stage attributes in order for you to get 2 columns) is a " " (space). If that is the case, then assuming you called your column id and names and the link is called InLink then the ereplace of

Code: Select all

Ereplace(InLink.names, "/", Char(013) : Char(010):InLink.id:' ', -1, 0):','

will generate output of

"1 aaa
1 bbb
1 ccc"

Is this not the format and content you want?

[Mark_E]

yes that's the output i require. i shall give that a go. thanks for all your help.