DSXchange

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

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.

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.

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.

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.

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

thanks again