Hi,
I have a source like this
ID-- Status-- Position-- Date
1 T S 01/20/2012
1 R S 08/20/2012
1 D S 09/20/2011
1 A S 10/20/2012
1 A S 11/18/2012
2 T S 02/22/2011
2 A L 08/15/2012
2 A S 08/15/2012
Output:
ID Status Position Date
1 A S 11/18/2012
2 A S 08/15/2012
Logic: If ID > 1 record then take record with status A and if A > 1 record take max effective date. even if Max eff date is same for two records then take record with position S.
How can I design this job with this logic?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your logic is not clear, particularly If ID > 1 (if this were part of the logic your output would have five rows with ID = 1). Can you please clarify the logic?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Sorry Ray,
What I mean was if we have more records with same ID number then take record with status A. Even if we have records with status A more than 1 for same ID then take the record which has max effective date. Even if we have more than 1 record with same Effective date then take record with position S. Please let me know I am ready to explain again if it is not clear enough.
What I mean was if we have more records with same ID number then take record with status A. Even if we have records with status A more than 1 for same ID then take the record which has max effective date. Even if we have more than 1 record with same Effective date then take record with position S. Please let me know I am ready to explain again if it is not clear enough.
Bhanu
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
Is status "A" special, or is it just the first letter in the alphabet?
Same with position "S". Is it special, or just the letter later in the alphabet?
What if you don't have an "A" record for an ID?
If it's just to do with their relative positions in the alphabet, you could sort on ID, status, date DESC, position DESC and then put it through a Remove Duplicates stage.
If you have specific priorities for status and position, either make reference tables with them in and give them a ranking, or use a Transformer to create the rankings. Then join with them before your sort (except now you sort on the rankings) and Remove Duplicates.
Same with position "S". Is it special, or just the letter later in the alphabet?
What if you don't have an "A" record for an ID?
If it's just to do with their relative positions in the alphabet, you could sort on ID, status, date DESC, position DESC and then put it through a Remove Duplicates stage.
If you have specific priorities for status and position, either make reference tables with them in and give them a ranking, or use a Transformer to create the rankings. Then join with them before your sort (except now you sort on the rankings) and Remove Duplicates.
G'day Bhana,
I'm inherently lazy so I'd read each record and create a primary and secondary priority for them.
If Status = 'A' the PRIORITY_1 = 1 else 0
IF POSITION = 'S' then PRIORITY_2 = 1 else 1
then sort on record_id, priority_1 desc, date desc, priotiy_2 desc.
RD on record_id, keeping first 1.
If the status and position may change, make them parameters to the job and use the parameter in the evaluation.
Handle Nulls as appropriate.
I'm inherently lazy so I'd read each record and create a primary and secondary priority for them.
If Status = 'A' the PRIORITY_1 = 1 else 0
IF POSITION = 'S' then PRIORITY_2 = 1 else 1
then sort on record_id, priority_1 desc, date desc, priotiy_2 desc.
RD on record_id, keeping first 1.
If the status and position may change, make them parameters to the job and use the parameter in the evaluation.
Handle Nulls as appropriate.
Andrew
Think outside the Datastage you work in.
There is no True Way, but there are true ways.
Think outside the Datastage you work in.
There is no True Way, but there are true ways.
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
You will need to provide better initial problem descriptions if you desire a higher degree of spoonfeeding.bond88 wrote:stuartjvnorton,
As of now its just letter A but its not always A and S so the sort logic you explained wont work. Could you please suggest some other solution (plz make A and S as generic/variable) then how can I handle the same job?
Thanks,