Problems using range lookup

[manuel.gomez]

Hello all,

I have been looking around the forum, and other webs, and it seems that new range lookup in Lookup stage will not help me to solve my problem, but I still do a try here.

I do have a table with a date, and a second one where I do have date intervals where I need to locate the key in the first table. Let me try to put this clearer:

STREAM LINK
Key Field: 7
Date: 2010-05-05

REFERENCE LINK
Key Field: 7
Start Date: 2010-01-01
End Date: 2010-03-03
Data: A

Key Field: 7
Start Date: 2010-03-04
End Date: 2010-07-07
Data: B


So I want to lookup data in order to obtain column 'Data' with value 'B'.

But it seems that, to use lookup stage, it should be the other way around: so the date interval comes in in the stream link, and the concrete date in the reference link. I cant do this way: my stream link consists of millions of records, while the interval reference link comes only with 20.000 records

I tried to trick Datastage this way:



But I got the following compiler error:

Code: Select all

Output from transformer compilation follows:

##I IIS-DSEE-TFCN-00006 19:51:09(001) <main_program> conductor uname: -s=AIX; -r=3; -v=5; -n=ammtbi; -m=00C8DC124C00
##I IIS-DSEE-TOSH-00002 19:51:09(002) <main_program> orchgeneral: loaded
##I IIS-DSEE-TOSH-00002 19:51:09(003) <main_program> orchsort: loaded
##I IIS-DSEE-TOSH-00002 19:51:09(004) <main_program> orchstats: loaded
##W IIS-DSEE-TOSH-00049 19:51:09(007) <main_program> Parameter specified but not used in flow: DSPXWorkingDir
##E IIS-DSEE-TFKL-00077 19:51:09(009) <transform> Error when checking composite operator: Error parsing range option.  Can't make a range out of 2 distinct fields "D_DAT_SAMPLE_DATE >= D_DAT_START_DATE AND D_DAT_SAMPLE_DATE >= D_DAT_START_DATE"
##E IIS-DSEE-TFKL-00077 19:51:09(010) <transform> Error when checking composite operator: Error parsing range option.  Can't make a range out of 2 distinct fields "D_DAT_SAMPLE_DATE <= D_DAT_END_DATE AND D_DAT_SAMPLE_DATE <= D_DAT_END_DATE"
##E IIS-DSEE-TFSR-00019 19:51:09(011) <main_program> Could not check all operators because of previous error(s)
##W IIS-DSEE-TFTM-00012 19:51:09(012) <transform> Error when checking composite operator: The number of reject datasets "0" is less than the number of input datasets "1".
##E IIS-DSEE-TCOS-00029 19:51:09(013) <main_program> Creation of a step finished with status = FAILED. (Job01_GET_LVC.Lookup1)

*** Internal Generated Transformer Code follows:
0001: //
0002: // Generated file to implement the V0S16_Job01_GET_LVC_Lookup1 transform operator.
0003: //
0004: 
0005: // define our input/output link names
0006: inputname 0 loadprf;
0007: tablename 0 hist;
0008: outputname 0 lkp_gis;
0009: 
0010: initialize {
0011: 	// define our row rejected variable
0012: 	int8 RowRejected0;
0013: 
0014: 	// define our null set variable
0015: 	int8 NullSetVar0;
0016: 
0017: }
0018: 
0019: mainloop {
0020: 	// initialise our row rejected variable
0021: 	RowRejected0 = 1;
0022: 
0023: 	// no condition for lookup link: hist
0024: 	lookup(hist);
0025: 	// evaluate columns (no constraints) for link: lkp_gis
0026: 	lkp_gis.N_COD_LVC_DWH_KEY = hist.N_COD_LVC_DWH_KEY;
0027: 	writerecord 0;
0028: 	RowRejected0 = 0;
0029: }
0030: 
0031: finish {
0032: }
0033: 
*** End of Internal Generated Transformer Code

I dont know how to move forward with this. The only solution I am having around in my mind is to use a join stage, sort stage, and then, a transformer with constraint to allow only one record per key, where input date is between start and end date

[ray.wurlod]

The range lookup can be performed on either link.

Research more deeply. Look in the manual or training materials for "range lookup on reference input" and "range lookup on stream input".

To be honest I have not yet found a need for the latter.

[vskr72]

I also had the same issue and I spent a great deal of time trying to get a solution. Finally I figured it out. This is what you need to do.

1. You should do all the lookups in the stream and so that will be a range
2. Build a reference in the following way:

Code: Select all

Key Field1: 7 
Key Field2: 7 
Start Date: 2010-01-01 
End Date: 2010-03-03 
Data: A 

Key Field1: 7 
Key Field2: 7
Start Date: 2010-03-04 
End Date: 2010-07-07 
Data: B 

3. Now apply the range lookup in the following way:

Code: Select all

1.  Stream.Key>= Ref.Key Field1 and Stream.Key<= Ref.Key Field2
2.  Stream.Date>= Ref.Start_DT and Stream.Date<= Ref.End_DT

This should work for you. Only thing that I realized is when you are trying to build the 2 key fields from reference input, try to dump it in a file and then use that for lookup. That was a weird requirement. But, it works. Let me know if that works. Thanks,

Satish

[Sreenivasulu]

I am curious to know how did you post a IMAGE in the forum- lookup diagram. Can you let me know ?

Regards
Sreeni

[chulett]

Oddly enough, by hosting the file somewhere and then linking to it using the "image" tags.

For the record, here's the tag from that post:

[img]http://img685.imageshack.us/img685/3461 ... lookup.jpg[/img]

[Sreenivasulu]

That's a great and simple idea.
Just upload a html to some ftp site and provide the link in the html tag.

Regards
Sreeni

[behrouz]

Hello all,

I have been looking around the forum, and other webs, and it seems that new range lookup in Lookup stage will not help me to solve my problem, but I still do a try here.

<snip>

hi all,
are you talking about image or issue

I think you are doing your range from your reference !!
why do not you do a simple lkp and then filter your lkp?
Arash.