Selecting Date which lies between 2 dates
Moderators: chulett, rschirm, roy
Selecting Date which lies between 2 dates
Hi,
I have a job in which I perform many look ups ......
Now I have various field on which I am performing a look up . I have a scenario whereI need tocheck if a Date lies between given 2 dates
If u talk in oracle terms it would be something like a where condition with
Table1.Date between table2.date1 and table2.date2
I am trying to do this in Xfm but I am not getting the required values.
Can anybody help me out pls
Thanks in advance
Preethi
I have a job in which I perform many look ups ......
Now I have various field on which I am performing a look up . I have a scenario whereI need tocheck if a Date lies between given 2 dates
If u talk in oracle terms it would be something like a where condition with
Table1.Date between table2.date1 and table2.date2
I am trying to do this in Xfm but I am not getting the required values.
Can anybody help me out pls
Thanks in advance
Preethi
What are you doing in the transformer? I mean how are you trying to implement this?I am trying to do this in Xfm but I am not getting the required values.
what are you looking up into? An ODBC? if so you could use the user defined sql option in the ODBC stage and specify the relational condition you require for the date field.
Dhiraj
Are you attempting a BETWEEN lookup using an ODBC/OCI stage? Or is your attempt to use hash files. Search this forum, as this topic is extensively covered.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Hi,
I just want to be clear in what I'm trying to acheive.
I am performing a look up on a key, and need to check the date range from the same hash, so I am trying to do the look up first and write a condition in the Transformer using a Stage Var like
If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD") and
Iconv(Table.A[1,10],"DYMD")<Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"
And my constraint would be
StageVar="Insert"
THis doesn't give me any error when I try to run this but only a few rows get processed
on the other hand if I give
If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD")
>Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"
then all the rows get processed. Both the results are wrong.
Can anybody help me with this?
Thanx
Preethi
I just want to be clear in what I'm trying to acheive.
I am performing a look up on a key, and need to check the date range from the same hash, so I am trying to do the look up first and write a condition in the Transformer using a Stage Var like
If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD") and
Iconv(Table.A[1,10],"DYMD")<Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"
And my constraint would be
StageVar="Insert"
THis doesn't give me any error when I try to run this but only a few rows get processed
on the other hand if I give
If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD")
>Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"
then all the rows get processed. Both the results are wrong.
Can anybody help me with this?
Thanx
Preethi
Step1:
Create a hash file (MyHash) with three fields:
1. Id -- Hard code this value with 'XYZ'
2. Date1 - integer - store your 1st date in the internal format (use iconv)
3. Date2 - integer - store your 2nd date in the internal format (use iconv)
Step2:
Do a look-up to Myhash with key expression = 'XYZ'
on the output, create a constraint like this:
Your input date (internal format -integer) > MyHash.Date1 and Your input date (internal format -integer) < MyHash.Date2
Reject any rows that does not satisfy this constraint to a reject file.
This is based on the assumption that you have only one set of date range.
Hope this helps.
Create a hash file (MyHash) with three fields:
1. Id -- Hard code this value with 'XYZ'
2. Date1 - integer - store your 1st date in the internal format (use iconv)
3. Date2 - integer - store your 2nd date in the internal format (use iconv)
Step2:
Do a look-up to Myhash with key expression = 'XYZ'
on the output, create a constraint like this:
Your input date (internal format -integer) > MyHash.Date1 and Your input date (internal format -integer) < MyHash.Date2
Reject any rows that does not satisfy this constraint to a reject file.
This is based on the assumption that you have only one set of date range.
Hope this helps.
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
Hi Preethi, I do not see anything wrong with this IF condition. I guess the date format you have given in Iconv ("DMYD") is not in sync with the date format coming from input\lookup tables. Just check that and give the proper date formats. It should work.pkl wrote: If ( Iconv(Table.A[1,10],"DYMD") > Iconv(Lookup.Date1[1,10],"DYMD") and
Iconv(Table.A[1,10],"DYMD")<Iconv(Lookup.Date2[1,10],"DYMD") ) then "Insert" else "Reject"
I had exactly the same requirement and I implemented it using the same logic. Its working fine for me.
Regards,
Sumit