based on Date comparison fetch exact value

[karteek]

Hi Everyone,

My source table Table1 contains columns WELLID,ORIG_REF, LOG_DATE

Sample data:

Code: Select all

WELL_ID	ORIG_REF	LOG_DATE
-------	--------	--------
1	      CALIB		11/19/2000
2	      BRITAN	  6/30/2006
3	      CALGRY	  NULL
4	      DENVAR	  8/20/2009
5	      ALBERTA	 NULL

i have to fetch one column from reference table : R_TABLE,
columns:[ORIG_REF,END_DATE,ENT_NAME] based on key column Table1.ORIG_REF

from this table i have to fetch ENT_NAME based on WELL_ID,END_DATE

reference table Sample data:

Code: Select all

WELL_ID	ORIG_REF	END_DATE	ENT_NAME		
-------	--------	--------	--------
1	      CALIB		11/19/2001    X
1	      CALIB		6/30/2006     Y
1	      CALIB		10/19/2000    Z
1	      CALIB		NULL          AB
2	      BRITAN	  6/30/2006     P
2	      BRITAN	  6/30/2004     Q
2	      BRITAN	  NULL          R
3	      CALGRY	  6/30/2006     C
3	      CALGRY	  6/30/2005     D
3	      CALGRY	  NULL          F
5	      ALBERTA	 6/30/2005     QA
5	      ALBERTA	 8/30/2005     CBZ

my requirement is:

condition #1:

for a given orig_ref:CALIB in reference table contains 4 records, from this i have to fetch ENT_NAME based on LOG_DATE and END_DATE.
i have to fetch ENT_NAME in such a way that LOG_DATE [ON or BEFORE] END_DATE.

i.e., log_date value : 11/19/2000, END_DATE values are [11/19/2001,6/30/2006,10/19/2000,NULL] here log_date on or before thatdate value i have to take from reference table,
value is 10/19/2000 hence for this record ENT_NAME: z this value i have to fetch.


condition #2:

for a given orig_ref:BRITAN in reference table contains 3 records, source log_date : 6/30/2006 exactly matches with END_DATE: 6/30/2006 hence
for this i have to fetch ENT_NAME: P


condition #3:

for a given orig_ref:CALGRY in reference table contains 3 records, source log_date : NULL, Reference table END_DATE also contains NULL value hence
for this i have to fetch ENT_NAME: F

note : if the reference table does'nt have value with NULL then hardcode ENT_NAME:'NOT APPLICABLE'

condition #4:

for a given orig_ref:ALBERTA in reference table contains 2 records,source log_date : NULL,BUT Reference table END_DATE contains date:6/30/2005,8/30/2005
here there is no NULL date in reference table hence hardcode ENT_NAME:'NOT APPLICABLE'


condition #5:

for a given orig_ref:DENVAR in reference table does't have any records for this hardcode ENT_NAME:'NOT APPLICABLE'


output :

Code: Select all

WELL_ID	ORIG_REF	LOG_DATE	ENT_NAME		
-------	--------	--------	--------
1	      CALIB	   11/19/2000	Z
2	      BRITAN     6/30/2006	 P
3	      CALGRY     NULL		   F
4	      DENVAR     8/20/2009	 NOT APPLICABLE
5	      ALBERTA    NULL		   NOT APPLICABLE

please help on this.

[chulett]

Welcome aboard.

First thing I have to ask is - how 'new to the product' are you? What have you tried? What issues are you having? Unfortunately, all it looks like right now is someone gave you an assignment / set of requirements and you came and posted them here. We're happy to help but you need to start the ball rolling.

[karteek]

Hi chulett,

I apologies on this, actually i tried with more logic. Unable to present my job design hence i didn't post what i am doing

for this in DataStage:

Code: Select all

		Reference table 
		|
		|
		V
step 1: src table  --	lkp[key:ORIG_REF]====Multiple Row out put=====> transformer stage [TFM_MATCHED]

		| Reject
		|
		V
		transformer[TFM_REJECTED][hardcode ENT_NAME:'NOT APPLICABLE'] 
		this stored into one dataset  [b]consider dataset as C[/b]

step 2: Transformer stage [TFM_MATCHED] :
two stage variables : 1.     SvLOGDATE:    JulianDayFromDate(lk_pass1.LOG_DATE)  this value i mapped to column: LGDAT of integer datatype
		           2.   SVE:      JulianDayFromDate((lk_pass1.END_DATE))
here I am converting dates into integer to calculate date difference

from Transformer stage  split into two ie., two constraints 
1. SvLOGDATE =SVE  this i am storing into one dataset       [b]consider dataset :A[/b]
2.SvLOGDATE<SVE
from this dataset  ==> copy stage ==>1. aggregator to calculate maximum END_DATE group by WELL_ID
				          2. second link used for lookup [stream]


copy : -----------------
|		lookup
|		|
V	-----------    based on LGDAT ------------step 3
aggregator 	

in step 3: transformer,     SORT  with hash partition  key column :LGDAT,    stage variable  X : LastRowInGroup(WELL_ID), constraint  : (X=1)

this value i am storing into one dataset. [b]consider dataset : B[/b]

to handle NULL: 

from source if log_date is null hardcoded as largest date : '2100-01-01', in the same way in reference table end_date also i hardcoded as '2100-01-01'

at last FUNNEL Of datasets : A,B,C

i tried with this, please advise on this. i am unable to show correct image of my job design.

[chulett]

First off, there's absolutely no need to send me your post in a Private Message... as you can see I found it just fine right here.

Secondly:

i am unable to show correct image of my job design.

Go back and look at your first post. Everything lines up there because I used code tags to preserve the whitespace and painstakingly hit 'Preview' and adjusted the result until it all lined up. Otherwise the forum software takes out all of the 'extra' spaces which makes it all mash over to the left. I don't have the time right now to do that again (or for a proper reply) perhaps you should take a shot at fixing your job design picture? Edit your post, add a pair of code tags around each and slide stuff around until it lines up under Preview... and note that your un-posted version will not look lined up.