DSXchange

Hi,

I have a requirement as follows.
there are 3 columns User,role, and code
sample records

U1,R1,D
U1,R1,U
U2,R1,D
U2,R2,U

and i need to select only

U2,R1,D
U2,R2,U

meaning i should not select if same user/role combination has 2 codes, in the above ex U1/R1 combination has two codes D and U and so need to reject both the rows

can i do this with the help of only a transformer stage and if so pls let me know.

Note : Records will be sorted on User/role keys.

Sure, stage variables doing normal 'group change detection' in a Transformer can handle this easily since the data is sorted properly. Keep track of the current and previous combination of User and Role, then only pass a record on the first time you see each particular combination.

thanks for ur reply, but my requirement is like i should not pass a record on the first time I see each particular combination, if combination repeats i should reject that combination entirely, whichever combination comes only one time i shud select that.

D'oh... misread things. Jury will disregard all statements made by the witness.

Obviously, you can't do this in a Transformer - at least not without some preprocessing or as the second of two passes through the data. First pass would be to determine which records should be 'rejected' and then a second to do the dirty deed.

Unless there's some magical way in PX to do this all at once? [shrug]

I don't see how this can be done using *just* a Transformer.

I had a similar problem and had used two sort stages with a transformer to remove these duplicates. Here is the post where Josh helped me out viewtopic.php?t=110447&highlight=

Hope that helps

Thanks for ur reply guys, it works fine.

We can do this using a single transformer, however it requires one dummy record at the end. This is because we don't know until next record is processed if the previous one qualifies to be outputted..

In the test map I developed, I added the dummy record using a row generator and funnelling(sequencially) along with input before hitting the transformer stage...

Here is what I did in the transformer..

--------------------------------------------------
Stage Variables:

sCount = sPrvCount
if ( ( sUser=x.User) and (sRole=x.Role) ) then sCount+1 else 1 = sCount

sUser=sPrvUser
sRole=sPrvRole
sValue=sPrvValue

lnk.User = sUser
lnk.Role = sRole
lnk.Value = sValue

-------------------------------------------------------------
Mapping

Link Condition = ( ( sPrvUser<>Lnk.User) or (sPrvRole<>Lnk.Role) ) and sPrvCount=1

sPrvUser = User
sPrvRole = Role
sPrvValue=Value

----------------------------------------------------------


Idea is simple as with other postings tp remove duplicates. However we need one extra 'count' stage variable to hold count of previous set of records. If this count=1, means the previous set of record have only one record so it will be allowed to pass the transfomer link.

The approach uses an additional row generator and a sequencial funnel, both are lite weight. However, if a dummy record say ZZ,ZZ,Z can be added part of file, we can then achive everything within the Transfomer stage..

--Duplicate posting deleted---

deleted

Rather than posting again to explain why it happened, next time just edit the duplicate post and then select the 'Delete' option. They can be deleted as long as there is no reply posted.