Date Comparison between group of input and Reference Records

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
senthilt1
Participant
Posts: 134
Joined: Mon Nov 19, 2007 2:17 am

Date Comparison between group of input and Reference Records

Post by senthilt1 »

Hi Team,

I need some help in doing this date comparison logic for the given input and reference file.

For the grouping rows on Column1 and Column2, i need to compare the from date and term date in input file with the reference file From date and Term Date. If the input file From date and Term date in the group falls within the Reference group rows Date column, then i need to pass the input as is to the Output.

Groupby Columns: Column1 and Column2 only


Inp file:

Column1, Column2, Colum3, Column4, FromDate, TermDate
10, 100, ABC, 123, 20170101, 20170301
10, 100, DEF, 988, 20170302, 20170701
10, 100, GHI, 895, 20170702, 20180101

Reference File:

Column1, Column2, Colum3, Column4, FromDate, TermDate
10, 100, ABC, 123, 20170101, 20170301
10, 100, DEF, 988, 20170302, 20170701

Expected Output: As the input rows From date and Term Date falls with in the From date and Term date of the Reference File. All the matched input rows to go the Target File.

Column1, Column2, Colum3, Column4, FromDate, TermDate
10 100 ABC 123 20170101 20170301
10 100 DEF 988 20170302 20170701
10 100 GHI 895 20170702 20180101

Any idea on implementing this logic would be of great help..

Thank You
Senthil
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Why is the third / "GHI" record in the expected output? I'm also curious if you have any examples of real data, not something that's obviously made up and which I worry may be far too simplistic. I'd like something that better illustrates your "falls within" requirement rather than "matches exactly", if you have some that would be great.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Seems to me you're needing a Range lookup in the Lookup stage. These are easy to construct; just follow the guidance in training/reference materials.

Just make sure that the data types are compatible (ideally identical).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Was thinking the same but hoping for more better data to confirm.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Actually, the design requires two Lookup stages executing in parallel streams; one to detect whether the start date of the period falls within the range specified on the other input, the other to detect whether the end date of the period falls within that range.

Alternately you could join on the other keys and use a complex nested If..Then..Else construct to effect the comparisons.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
senthilt1
Participant
Posts: 134
Joined: Mon Nov 19, 2007 2:17 am

Post by senthilt1 »

Thank You for the Inputs,
will try on this logic mentioned.
Post Reply