how to group data and check some constraint

[smitageorge]

Hi,

Does anybody had a idea to check the constraint for a group of data?

I am having input as:

ID MO DA CA
1 0 M COOL
1 0 D COOL
1 0 R FOOL
1 1 M COOL
1 1 D COOL
1 1 R COOL
1 2 D COOL
1 2 R COOL
1 3 D COOL
2 0 M COOL
2 1 R COOL
3 2 M COOL
3 2 R FOOL


and so on.........

and i need to obtain my output as

ID MO DA CA
1 1 M COOL
1 1 D COOL
1 1 R COOL
1 2 D COOL
1 2 R COOL
1 3 D COOL
2 0 M COOL
2 1 R COOL

Here for the group(ID) we had to check the MO,if MO is same for incoming rows check the CA and if the CA is same for all the MO's then pass that to the output orelse reject that particular group.

Please anybody can pass some light on this.It is highly appreciated.

Thanks
Smita

[vsurap]

I guess the below logic should work.

Input----->Remove Dups(Key: ID,MO and CA)--->Agregater (Key: ID,MO) get the Count rows--->
Transformer( Constraint: if count Rows=1)---> To Lookup(Reference Stream)


Input -----> Lookup(Key: ID and MO) ---> Ouptput

[kumar_s]

What you mean if 'MO is same for incoming rows '... Do you have any reference data?

[ray.wurlod]

Use stage variables to identify the group and the first value of CA in each group, and set a flag if a different CA value is found in the same group. Keep a list of groups and flags, then post-process the data so that groups can be kept or rejected.

[smitageorge]

Use stage variables to identify the group and the first value of CA in each group, and set a flag if a different CA value is found in the same group. Keep a list of groups and flags, then post-process ...

Can you elaborate it little more.

Thanks
smita

[ray.wurlod]

A stage variable to record whether you're in a new group. A stage variable to "remember" the previous group. A stage variable to record the CA value on group change only. A stage variable to compare CA against that one (this is the "flag"). Output all input columns and flag. Run through Aggregator capturing last flag value. Switch stage to segregate based on flag.

[smitageorge]

What you mean if 'MO is same for incoming rows '... Do you have any reference data?

No there is no reference data.I believe i had not given the proper description:

There is a source file with all these fields and i need to load the transformed data in the sequential file.

I want the output in the given format.

1. Need to group the data depending on the ID and MO
2. For each group check if CA is same for all the possible combinations of DA.

Possible conditions(M/D/R, M/D, M/R, D/R, M, D, R )
3. If it is same for the group pass the data to sequential file or else drop the data in the reject file.

I need to give the constraints in the Transformer.

Thanks
Smita

[us1aslam1us]

A stage variable to record whether you're in a new group. A stage variable to "remember" the previous group. A stage variable to record the CA value on group change only. A stage variable to compare CA against that one (this is the "flag"). Output all input columns and flag. Run through Aggregator capturing last flag value. Switch stage to segregate based on flag.

Hi Ray i am having the similar requirement.I understood what u r trying to say here but i am not very familiar with stage variables use. so could you please help me in the job design at your convinience.

Thanks
sam

[ray.wurlod]

Given that I'm fully booked through to the end of June "at my convenience" may be a little long.
Why not play with them? It's a great way to learn. It's how I learn.

[us1aslam1us]

Given that I'm fully booked through to the end of June "at my convenience" may be a little long.
Why not play with them? It's a great way to learn. It's how I learn.