DSXchange

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

You can use Aggregator stage to group my the key value

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

What you need to do is called a vertical pivot and an exact search would reveal many discussions here with several solutions.

Also what you could do is to Use Stage variables and compare with the previous ID and make sure the ID is sorted.

Which is one of the solutions that a search would reveal.