DSXchange

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

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.

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).

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

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.

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