Page 1 of 2
Date in Lookup Stage
Posted: Wed Apr 18, 2007 8:29 am
by pravin1581
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 "
Posted: Wed Apr 18, 2007 8:32 am
by DSguru2B
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.
Posted: Wed Apr 18, 2007 8:49 am
by kris007
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.
Posted: Wed Apr 18, 2007 8:51 am
by pravin1581
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.
I have seen that post where am I suppose to incorporate the ""failed lookup" rule is set to other values.
Posted: Wed Apr 18, 2007 9:08 am
by pravin1581
pravin1581 wrote: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.
I have seen that post where am I suppose to incorporate the ""failed lookup" rule is set to other values.
In my case there is no properties tab in the look up stage .
Hi
Posted: Wed Apr 18, 2007 9:25 am
by smrutiranjannayak
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 
Posted: Wed Apr 18, 2007 9:36 am
by kris007
pravin1581 wrote: In my case there is no properties tab in the look up stage .
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.
Posted: Wed Apr 18, 2007 4:12 pm
by ray.wurlod
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.
Posted: Wed Apr 18, 2007 10:02 pm
by pravin1581
kris007 wrote:pravin1581 wrote: In my case there is no properties tab in the look up stage .
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.
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.
Posted: Wed Apr 18, 2007 10:09 pm
by ag_ram
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
Posted: Wed Apr 18, 2007 10:29 pm
by pravin1581
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
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.
Posted: Wed Apr 18, 2007 10:50 pm
by ray.wurlod
Add a reject link to the Lookup stage so that you can see exactly what values were sought.
Posted: Wed Apr 18, 2007 10:59 pm
by pravin1581
ray.wurlod wrote:Add a reject link to the Lookup stage so that you can see exactly what values were sought.
The problem is the output file does not gets populated for those id's where there are matching datas for the date field. The id field is empty for the entire output file.
Posted: Wed Apr 18, 2007 11:26 pm
by ray.wurlod
I wrote:Add a reject link to the Lookup stage so that you can see exactly what values were sought.
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?
Posted: Thu Apr 19, 2007 12:55 am
by pravin1581
ray.wurlod wrote:I wrote:Add a reject link to the Lookup stage so that you can see exactly what values were sought.
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 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.