Date in Lookup Stage

pravin1581 · Post by **pravin1581** » Wed Apr 18, 2007 8:29 am

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 "

DSguru2B · Post by **DSguru2B** » Wed Apr 18, 2007 8:32 am

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.

kris007 · Post by **kris007** » Wed Apr 18, 2007 8:49 am

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.

pravin1581 · Post by **pravin1581** » Wed Apr 18, 2007 8:51 am

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.

pravin1581 · Post by **pravin1581** » Wed Apr 18, 2007 9:08 am

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 .

smrutiranjannayak · Post by **smrutiranjannayak** » Wed Apr 18, 2007 9:25 am

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

kris007 · Post by **kris007** » Wed Apr 18, 2007 9:36 am

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.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 4:12 pm

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.

pravin1581 · Post by **pravin1581** » Wed Apr 18, 2007 10:02 pm

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.

ag_ram · Post by **ag_ram** » Wed Apr 18, 2007 10:09 pm

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

pravin1581 · Post by **pravin1581** » Wed Apr 18, 2007 10:29 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 10:50 pm

Add a reject link to the Lookup stage so that you can see exactly what values were sought.

pravin1581 · Post by **pravin1581** » Wed Apr 18, 2007 10:59 pm

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.

ray.wurlod · Post by **ray.wurlod** » Wed Apr 18, 2007 11:26 pm

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?

pravin1581 · Post by **pravin1581** » Thu Apr 19, 2007 12:55 am

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.