Hi,
I've a scenario:
For example, say a student id record has been received that has an id#: 123 in Table A and a duration of 5 hours.
123 has 2 courses QW, PO associated with it in Table B. The record for student id: 123 will therefore be divided into 2 training records;
Record1: QW with Duration: 2 hrs 30 mins
Record2: PO with duration: 2 hrs 30 mins.
I'm not able to to generate the Distribution Duration field which has logic divide the field with number of reecords found for particular student id
below is an example
Input
Table A
Student id | Duration
123 | 5
124 | 6
Table B
Student id | Course
123 | QW
123 | PO
124 | AWER
124 | ASD
Output
Student id | Course | Distributed Duration
123 | QW | 2.5
123 | PO | 2.5
124 | AWER | 3
124 | ASD | 3
Thanks in Advance
Shreya
Process
Moderators: chulett, rschirm, roy
Re: Process
Hi Shreya,
First you need to find out how many different courses are there for each Student id. so you can refernce it and calculate distributed duration for each student id.
May be you can do all in one sql but am giving you this simple solution on top of my head.
Since you have those records in Tables. Do this userdefined sql in your database stage and write records to hashfile call it as 'tableC' with student_id as key. this can be a separate job or can do it in the main job.
Now the main job:
Join TableA and TableB(inner join on student_id) in database stage and drive student_id, course, duration into transformer and do a lookup on TableC. You can calculate distributed duration (which would be duration/HOWMANY) in derivation and map it into the target. Don't forget to put constraint to make your lookup as inner join which you can give in different ways....................................one would be
Should work for you.
Kris~
Here is one solution to this problem.shrey3a wrote: Input
Table A
Student id | Duration
123 | 5
124 | 6
Table B
Student id | Course
123 | QW
123 | PO
124 | AWER
124 | ASD
Output
Student id | Course | Distributed Duration
123 | QW | 2.5
123 | PO | 2.5
124 | AWER | 3
124 | ASD | 3
First you need to find out how many different courses are there for each Student id. so you can refernce it and calculate distributed duration for each student id.
May be you can do all in one sql but am giving you this simple solution on top of my head.
Since you have those records in Tables. Do this userdefined sql in your database stage and write records to hashfile call it as 'tableC' with student_id as key. this can be a separate job or can do it in the main job.
Code: Select all
select student_id, count(*) as HOWMANY from TableB group by student_id;
Join TableA and TableB(inner join on student_id) in database stage and drive student_id, course, duration into transformer and do a lookup on TableC. You can calculate distributed duration (which would be duration/HOWMANY) in derivation and map it into the target. Don't forget to put constraint to make your lookup as inner join which you can give in different ways....................................one would be
Code: Select all
NOT(IsNull(hashfilelink.student_id))
Kris~
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom