DSXchange

If for a account number there are multiple values are present in another field, how can I take each value and propagate it to different fields.For e.g. if there are multiple dates for a single account no coming from a single source field,and in the target there are different target columns are present where we need to propagate the respective values.The first date should go to the first column, the second should go to the second column and thus so on.How to implement this in datastage PX.For a single account no the date field which is having multiple values is not coming in a single row with a seperator, they are coming in different rows in a distinct field.

(there is no need to open up a new thread, just reply to the previous one)

Again, could you post an example. Is the data in the form of:

Acct1,Date1
Acct1,Date2
...
Acct2,Date1

or

Acct1,Date1,Date2,Date3,...

or

"Acct1","Date1,Date2,Date3"

yes, Acct1,Date1 Acct1,Date2 ... Acct1,Daten in multilpe rows for the same account no i.e. there are multiple date vales for a single account no , and we need to take sort the date and then min date need to go to dat1, next one to dat2 and so on in different target columns for the same account no.

How do you think that will work ?

Firstly, having multiple dates will make your target structure dynamic. i.e. the first account may have 2 dates whereas the second may have 10.

So you must have some fixed number of date columns.

Search for "Vertical Pivot" and use stage variables to arrange the dates.

Alternatively you can use the database query to provide the dates in order in a subquery and select rownum in the main. This will assist you to order using the rownum column.

The output columns are restricted and the no of distinct dates for a particular account no will not go beyond that.

Try the following:

If the number of output date columns is fixed then you could have that many output links from a transformer stage and extract and pass the date values into the output.

So , if you have 10 output date field in the Table, create 10 output links from a transformer and pass one date field into each of the 10 outputs.

To extract the individual date fields you could use the Field function is the transformer.

Then , funnel all these links into a single output link. Now you will end up with one record for every Acct - Date combination. This entire activity can also be done in an external script Or a BuildOp.

Next step would be to sort the data on the Acct - Date combination so that for each account , your dates are in the ascending or desending order.

Next , you will have to do a kind of a Reverse Pivot , where where you plug in each date into the corresponding output Date column. To do this you will have to use a combination of a transformer stage and also a remove duplicate stage.