Manipulation of data

[Mark_E]

hi,

i currently have two fields in my table (prodid, status) if the Status is 'A' for a Prodid then i need to set all Statuses for that ProdId to 'A' else leavealone. Example below:

ProdId: Status:
1 A
1 I
1 B
2 I
3 I

The results i require as:

ProdId: Status
1 A
1 A
1 A
2 I
3 I

Just cant seem to change it for the specific ProdId. Any assisstance greatly appreciated. thanks in advance guys

[Mark_E]

sorry i forgot to mention the Status field isnt in a table, it is generated within my job to obtain the single character values as described.

[narasimha]

You can use "RowProcCompareWithPreviousValue" or "Stage Variables" to achieve it.
If not same but similar situtaions have been discussed here a few times. It would get you started.

[us1aslam1us]

Filter the records for status='A' and load them into the hashed file. Use this Hashed file to do a lookup on your source file based on ProdId as the key.

[chulett]

If there's no order to the Status data, meaning you could get something like this:

5 I
5 B
5 B
5 A

Then it seems to me you'll need to make two passes through the data. You could use a hashed file that you wrote to and read in the same transformer, to build a list of ProdIds and record if you ever saw an 'A' in the first pass. Then a second pass to use the hashed lookup to determine if any given ProdId should get all A's or be left alone.

[Mark_E]

Thanks for all your points, appreciate it. i will give that a go and let you know how i get on.

thanks again