capture records that exceed the limit
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
capture records that exceed the limit
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
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
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
(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
Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives.
By William A.Foster
By William A.Foster
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
capture records that exceed the limit
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
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
-
- Participant
- Posts: 27
- Joined: Thu Mar 20, 2008 11:55 am
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
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
Last edited by filename.txt on Tue Dec 02, 2008 1:24 pm, edited 1 time in total.
Thanks.
"Creativity is the ability to use your available resources to their fullest."
"Creativity is the ability to use your available resources to their fullest."
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
capture records that exceed the limit
Thank you for the suggestion.
can you please tell me what will be the initial value for stage variable
"prevrow_student".
Thanks
Mark
can you please tell me what will be the initial value for stage variable
"prevrow_student".
Thanks
Mark
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
capture records that exceed the limit
Thank you for the suggestion.
can you please tell me what will be the initial value for stage variable
"prevrow_student".
Thanks
Mark
can you please tell me what will be the initial value for stage variable
"prevrow_student".
Thanks
Mark
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
capture records that exceed the limit
Thanks Mike.
I am sorting the data in ascending order based on department + student to capture only those records exceeding the limit.
I am sorting the data in ascending order based on department + student to capture only those records exceeding the limit.
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
capture records that exceed the limit
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
Please advice me.
Thanks
Mark
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
capture records that exceed the limit
[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
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
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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.
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.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!