Hi Everyone,
I have the following data:
ID_NUMBER KEY
100 AAA
100 BBB
from the above data if the ID_NUMBER is same then I have to concate nate KEY values, the output should look like:
ID_NUMBER KEY
100 AAABBB.
I should get as one record.
Could someone help how this can be achieved in Datastage
Many Thanks
Cheers..
Cherry
Concatenation
Moderators: chulett, rschirm, roy
Concatenation
hi Cherry ,
i have a solution for your problem but it is little long
first what we need here to convert rows to one row , with concatenated columns , also we may want preserve ordring 'if you nedd the output to bee AAABBB not BBBAAA' so let's go
first you will need to read the data twice , i.e. the same data , read them twice , one of the output to Surrogate_Key_Generator to get an id , the output of the Surrogate_Key_Generator and the second input , are connected together to join using the original id and output the generated key , the original id , the key from tghe first link key1 and the key from the second link key 2 , so now we have this output
gen_id id key1 key2
1 100 AAA AAA
1 100 AAA BBB
2 100 BBB AAA
2 100 BBB BBB
the second step is we need the first row with different values at key1 and key2 ,,, so what we need now is a transformer with constraints that select only data with key1 <> key2 ,, and concatenat the two columns key1 and key2 so we have the following output
gen_id id key
1 100 AAABBB
2 100 BBBAAA
now the final step , remove duplicates stage , choosing the minimum gen_id ,,, that what do you want ,,,
but this method works only with two rows with the same ID , i.e if you have data like
100 AAA
100 BBB
100 CCC
this method doesn't work
this is intial solution , and i'll try to found smarter one ,,,
never hesitate to ask at any time
Gendy
i have a solution for your problem but it is little long
first what we need here to convert rows to one row , with concatenated columns , also we may want preserve ordring 'if you nedd the output to bee AAABBB not BBBAAA' so let's go
first you will need to read the data twice , i.e. the same data , read them twice , one of the output to Surrogate_Key_Generator to get an id , the output of the Surrogate_Key_Generator and the second input , are connected together to join using the original id and output the generated key , the original id , the key from tghe first link key1 and the key from the second link key 2 , so now we have this output
gen_id id key1 key2
1 100 AAA AAA
1 100 AAA BBB
2 100 BBB AAA
2 100 BBB BBB
the second step is we need the first row with different values at key1 and key2 ,,, so what we need now is a transformer with constraints that select only data with key1 <> key2 ,, and concatenat the two columns key1 and key2 so we have the following output
gen_id id key
1 100 AAABBB
2 100 BBBAAA
now the final step , remove duplicates stage , choosing the minimum gen_id ,,, that what do you want ,,,
but this method works only with two rows with the same ID , i.e if you have data like
100 AAA
100 BBB
100 CCC
this method doesn't work
this is intial solution , and i'll try to found smarter one ,,,
never hesitate to ask at any time
Gendy
Mohmmed Elgendy
Senior System Analyst
Data IntegrationTeam
Etisalat Egypt
+20 1118511161
Senior System Analyst
Data IntegrationTeam
Etisalat Egypt
+20 1118511161
