How can I design this job with this logic?

[bond88]

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.

[ray.wurlod]

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?

[bond88]

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.

[stuartjvnorton]

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.

[bond88]

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,

[aartlett]

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.

[stuartjvnorton]

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,

You will need to provide better initial problem descriptions if you desire a higher degree of spoonfeeding.