Date in Lookup Stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
Date in Lookup Stage
Hi All,
I am having problem when i use Date as a look up column in the look up stage between 2 files . The job is getting aborted with the error " Failed a key look up for record "
I am having problem when i use Date as a look up column in the look up stage between 2 files . The job is getting aborted with the error " Failed a key look up for record "
Its not because of the Date column and search would have revealed that. Please search on the error message. This is one such post on a similar issue.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
The job aborts as per the message, there is no match for the date coming from the input file to the date on your lookup file. You will need to handle the rows which fail the lookup correctly i.e. in most cases collect them through a reject link. You can set that property within the lookup stage.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
I have seen that post where am I suppose to incorporate the ""failed lookup" rule is set to other values.DSguru2B wrote:Its not because of the Date column and search would have revealed that. Please search on the error message. This is one such post on a similar issue.
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
In my case there is no properties tab in the look up stage .pravin1581 wrote:I have seen that post where am I suppose to incorporate the ""failed lookup" rule is set to other values.DSguru2B wrote:Its not because of the Date column and search would have revealed that. Please search on the error message. This is one such post on a similar issue.
-
- Participant
- Posts: 22
- Joined: Mon Apr 02, 2007 4:02 am
- Location: MUMBAI
- Contact:
Hi
Hi
Pravin I think u must be aware of the universal fact that look up works on key....and at same time the data u want to fetch cant be the key.....ok...
So for ur case i mean if u want date in lookup(hash file if server edition) then what u can do is you can create a dummy column and validate the same in ur main job....
In detail :
You have empId only as 1 column
1) create a simple job which will create ur Has file containing empid along with a dummy column and give sm values to that column say 1
2) In ur main job use the value 1 in hash file key value to get records from hash file
Cheers
Pravin I think u must be aware of the universal fact that look up works on key....and at same time the data u want to fetch cant be the key.....ok...
So for ur case i mean if u want date in lookup(hash file if server edition) then what u can do is you can create a dummy column and validate the same in ur main job....
In detail :
You have empId only as 1 column
1) create a simple job which will create ur Has file containing empid along with a dummy column and give sm values to that column say 1
2) In ur main job use the value 1 in hash file key value to get records from hash file
Cheers
Smrutiranjan Nayak
There won't be a lookup stage without the properties tab. You can find that at the top left corner once you open the stage which reads as 'Stage Properties'. However, the tab you will need to lookout for is 'Constraints' which you can find beside the stage properties tab.pravin1581 wrote: In my case there is no properties tab in the look up stage .
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I suspect that your "lookup failed" rule is set to "Fail" (its default value).
Click on the second tool from the left to open the rules grid. The lookup failed rule is on the right hand end.
Press F1 with that dialog open to get an explanation of what each of the four possible settings does.
Click on the second tool from the left to open the rules grid. The lookup failed rule is on the right hand end.
Press F1 with that dialog open to get an explanation of what each of the four possible settings does.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
There is a stage properties tab inder which there are three tabs such as stage, input ,output . There should be a properties tab under the stage tab according to the manuals , which is not there.kris007 wrote:There won't be a lookup stage without the properties tab. You can find that at the top left corner once you open the stage which reads as 'Stage Properties'. However, the tab you will need to lookout for is 'Constraints' which you can find beside the stage properties tab.pravin1581 wrote: In my case there is no properties tab in the look up stage .
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
I have set that to continue, the job is running fine but it is not returning the desired data , i mean where the 2 files have the matching date field it sholud return the corresponding id's of the date in the output file according to the design. The output file has empty values for the id's.ag_ram wrote:Hi Pravin
To set the property to reject , you have go to the constraints and set lookup to drop/continue/fail/reject . You have set it to fail and thats y the job ends with a failure
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What happened when you tried that? What value was in the date column that you used for a key, and subsequently was sent to the reject link?I wrote:Add a reject link to the Lookup stage so that you can see exactly what values were sought.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 497
- Joined: Sun Dec 17, 2006 11:52 pm
- Location: Kolkata
- Contact:
I have defined the date field as the VARCHAR data type in both the files , may be that is the issue of the data not matching.ray.wurlod wrote:What happened when you tried that? What value was in the date column that you used for a key, and subsequently was sent to the reject link?I wrote:Add a reject link to the Lookup stage so that you can see exactly what values were sought.