Hi,
I have a requirement like this . I have a input record like this
ID NO COUNTRY PRICE VALIDFROM
9406 123 US 00 1/1/2007
9406 123 US 00 1/1/2004
9406 123 US 00 1/1/2005
Now I want to take latest VALIDFROM and assign VALIDTO as 999/12/31 and remaining records must contain Newprice.Validto - Oldprice.Validfrom-1
output is
ID NO COUNTRY PRICE VALIDFROM VALIDTO
9406 123 US 00 1/1/2007 9999/12/31
9406 123 US 00 1/1/2005 2006/12/31
9406 123 US 00 1/1/2004 2004/12/31
How can I achieve this using a stage variable. Is this possible using stage variable .
Thanks,
SomaRaju.
Stage Variable
Moderators: chulett, rschirm, roy
Stage Variable
somaraju
Re: Stage Variable
Hehsomu_june wrote:Hi,
I have a requirement like this . I have a input record like this
ID NO COUNTRY PRICE VALIDFROM
9406 123 US 00 1/1/2007
9406 123 US 00 1/1/2004
9406 123 US 00 1/1/2005
Now I want to take latest VALIDFROM and assign VALIDTO as 999/12/31 and remaining records must contain Newprice.Validto - Oldprice.Validfrom-1
output is
ID NO COUNTRY PRICE VALIDFROM VALIDTO
9406 123 US 00 1/1/2007 9999/12/31
9406 123 US 00 1/1/2005 2006/12/31
9406 123 US 00 1/1/2004 2004/12/31
How can I achieve this using a stage variable. Is this possible using stage variable .
Thanks,
SomaRaju.
Yup doing it in Stage variables is a way..let me give you a hint
Hint 1: you use place holders for the keys ..like current key and previous key
Hint 2: you use flags- like Change flag..the derivation should be if current key <> previous key then 1 Else 0
Hint 3: you also have two place holders for date: Current End date and Previous Effective Date
Hint 4: you hold the value of the current End date = previous effective date
Hint 5: you reassign the date place holders if Change Flag is 0 Else continue
Try to write the algo on paper........if you need some more clue google for bubble sort..it is a similar method...try it else ask for more clues
Avishek Mukherjee
Data Integration Architect
Chicago, IL, USA.
Data Integration Architect
Chicago, IL, USA.