capture records that exceed the limit

[pavan_test]

Hi All,

I have a problem.

department students limit
1 10 10
1 9 10
1 15 10
2 12 10
2 10 10
3 10 10

If students in each department exceed the limit then capture the department,students and limit in a reject link
and others capture in output link.

output link:
(Students <= limit, Based on the department ---> capture in output link)

department students limit
1 10 10
1 9 10
2 10 10
3 10 10

Reject Link:

(students > limit, Based on the department ---> capture in reject link)

department students limit
1 15 10
2 12 10

Anyone please suggest me how do i appraoch this problem.

Thanks
Mark

[Mike]

Am I missing something in your requirement? If you've used the product at all this is pretty trivial... transformer, output link, reject link, output link constraints.

Mike

[srimitta]

output link: - Tranform Stage Constraint logic student <= limit
(Students <= limit, Based on the department ---> capture in output link)
department students limit
1 10 10
1 9 10
2 10 10
3 10 10


Reject Link: Tranform Stage Constraint logic student > limit

(students > limit, Based on the department ---> capture in reject link)

department students limit
1 15 10
2 12 10

[pavan_test]

sorry its my bad for not posting the complete question.
i have a constraint in my transformer and i have my data like this.


department limit Roll student
1 2 4 A
1 2 4 B
1 2 4 C --->capture this which exceeded the limit
1 2 4 D ---> capture this which exceeded the limit
2 1 2 E
2 1 2 F ---> capture this which exceeded the limit


The business rule is to sort the "student" column in ascending order before capturing those records that exceeded the limit which i did.

Can anyone please suggest me how do I get my desired output;


Desired output:

department limit roll student
1 2 4 C
1 2 4 D
2 1 2 F

Thanks
Mark

[filename.txt]

Take logic like this,...

in transformer define a stage variable

if prevrow_dept=current_dept then stgvar1=stgvar1+1 else stgvar1=0

in columns give the below expression..
if stgvar1>inrow.limit then inrow.student else setnull() --for outrow.student

and put output constrain outrow.student Not null

[Mike]

Is your student order always correlated with your department order? Seems like you should be sorting on department + student if you're looking to capture the students that exceed a department limit.

For your simple example, what happens when you add another student G for department 1?

Mike

[pavan_test]

Thank you for the suggestion.

can you please tell me what will be the initial value for stage variable
"prevrow_student".

Thanks
Mark

[pavan_test]

Thank you for the suggestion.

can you please tell me what will be the initial value for stage variable
"prevrow_student".

Thanks
Mark

[pavan_test]

Thanks Mike.
I am sorting the data in ascending order based on department + student to capture only those records exceeding the limit.

[pavan_test]

I sorted the data based on department + student and added this logic. i was not sure the initial value for prevrow_student. i assigned the prevrow_student as current_student. it did not give me my desired output.

Please advice me.

Thanks
Mark

[Mike]

The order of stage variable evaluation is critical.

Since you've indicated that you're doing a parallel job, do be sure to manage your partitioning correctly so that your row to row comparisons will be valid...

Mike

[pavan_test]

[quote="filename.txt"]Take logic like this,...

in transformer define a stage variable

if prevrow_dept=current_dept then stgvar1=stgvar1+1 else stgvar1=0

in columns give the below expression..
if stgvar1>inrow.limit then inrow.student else setnull() --for outrow.student

and put output constrain outrow.student Not null[/quote]

Thank for the suggestions. This logic is working fine when i have 1 department.
when the incoming data has multiple departments (1,2,3 etc) the logic is taking only the department 1.

Can anyone please give me suggestions.

Thanks
Mark

[kandyshandy]

Try like this:

Define stage variable in the same order as shown below.

current_dept : #dept from input#
stgvar1 : if prevrow_dept=current_dept then stgvar1+1 else 1
extract_flag : if stgvar1 <= #limit from input# Then 'Y' Else 'N'
prevrow_dept : current_dept

constraint in transformer : extract_flag = 'Y'

As Mike said, take care of the sort/partitioning. The trick is... sort by both department and student but partition only by department.