Repetition of data
Moderators: chulett, rschirm, roy
Repetition of data
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
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
mark_e
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?
if I understand your requiement correctly, this is a perfect example for the use of the PIVOT stage; had you looked into that?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
mark_e
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 and then ignore the last line (which would just contain an id but no data.
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)
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
mark_e
Mark_E,
look at my ereplace suggestion a bit more closely; and add a column separator character (don't know what you used...).
look at my ereplace suggestion a bit more closely; and add a column separator character (don't know what you used...).
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
mark_e
Mark,
what is your column separator in your text file? If it is a comma, then make the ereplace read:
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):','
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
mark_e
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 will generate output of
"1 aaa
1 bbb
1 ccc"
Is this not the format and content you want?
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):','
"1 aaa
1 bbb
1 ccc"
Is this not the format and content you want?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>