condition in a transformer

[fmartinsferreira]

How does that affect the calculations?? you are doing lookup matching all your columns in lookup file with the main file?

Krazykoolrohit it's almost this.

main input = LINK_001
main_id
dt_record

lookup input = LINK_002
lookup_id
lookup_desc
dt_start
dt_end

rule: Only load the records where main_id = lookup_id
and dt_record between dt_start and dt_end.

I need to match:
LINK_001.main_id = LINK_002.lookup_id
LINK_002.dt_start <= LINK_001.dt_record
LINK_001.dt_record <= LINK_001.dt_end

The objective here is get the correct description (LINK_002.lookup_desc) when this description was valid.
For example: To the same ID(LINK_002.lookup_id) I can have one description for a month and other description to the next month. Something like

lookup_id lookup_desc dt_start _________ dt_end
1_______ AAA _______ 01/01/2005_______ 10/07/2005
1_______ BBB _______ 10/08/2005_______ 01/10/2005
1_______ CCC_______ 02/10/2005_______ 10/01/2006

Well, for the ID = 1 the correct description in 10/09/2005 is "BBB" but in 03/11/2005 the correct description is "CCC".

Thanks,

Fernando

[DSguru2B]

Ok, this is a little bit tricky.
Before building the hashed file, add another column which is a surrogate key, a running number, i.e, 1,2,3..... and so on. Specify that as key and in the derivation of the lookup specify @INROWNUM so that each and every row has a hit.
Define a stage variable to check for the keys. Say 'cond'
The logic of 'cond' will be

Code: Select all

if  NOT(RefLink.NOTFOUND) AND main_id = lookup_id AND ICONV(dt_record,"D/MDY[2,2,4]") >= ICONV(dt_start,"D/MDY[2,2,4]") AND ICONV(dt_record,"D/MDY[2,2,4]") <= ICONV(dt_end,"D/MDY[2,2,4]") then 'P' else 'N'

in the constraint of the output link, specify

Code: Select all

cond = 'P'

This way only those rows will pass that specify the above condition.

[rachitha]

hi..

take odbc lookup , in that specify start and end dates as keys and select userdefined query. go to view query there you change

"start_date< ? and end_date> ?" just you have to change "=" to < and > as i have mentioned in double quotes.

then in transformer give link from
1) src_date to start_date and target_date.
2) main_id to lkp_id also

this will solve your prb. if any queries plz reply back..

thank you
rachitha.
Be Kewl

I need to apply one condition in a transformer and I'm little lost.

rule: Only load the records where main_id = lookup_id
and dt_record between dt_start and dt_end.

- main input I have main_id and dt_record
- lookup input I have lookup_id, lookup_desc, dt_start and dt_end.
- target I have main_id, lookup_desc and dt_record

for example:

main input
main_id dt_record
1 10/10/2005
2 10/10/2005
3 10/09/2005

lookup input
lookup_id lookup_desc dt_start dt_end
1 AAA 01/01/2005 10/07/2005
1 BBB 10/08/2005 01/10/2005
1 CCC 02/10/2005 10/01/2006

if I have this sources, what I shoud load in the target is:

main_id lookup_desc dt_record
1 CCC 10/10/2005

Additional info:
main input -> store procedure Sql Server
lookup input -> hashed file(all the columns in the hashed file is key)

I would like to know what key expression should I put in the transformer?

Regards,

Fernando

[Krazykoolrohit]

one of the simplest method can be.

Join both the files. do an right outer join with ID as key . so you have all 1s joined with all instances of 1 from the refernece file.

in the subsequent transformer check if the date from you main flow is in between the range you got after the join. if not, drop the record.

Table 1
main_id dt_record
1 10/10/2005
2 10/10/2005
3 10/09/2005

Table 2
lookup_id lookup_desc dt_start dt_end
1 AAA 01/01/2005 10/07/2005
1 BBB 10/08/2005 01/10/2005
1 CCC 02/10/2005 10/01/2006



After join
main_id dt_record lookup_desc dt_start dt_end
1 10/10/2005 AAA 01/01/2005 10/07/2005
1 10/10/2005 BBB 10/08/2005 01/10/2005
1 10/10/2005 CCC 02/10/2005 10/01/2006

Now apply your conditions.

let me know in case i have still not understood your query

[fmartinsferreira]

Ok, this is a little bit tricky.
Before building the hashed file, add another column which is a surrogate key, a running number, i.e, 1,2,3..... and so on. Specify that as key and in the derivation of the lookup specify @INROWNUM so that each and every row has a hit.
Define a stage variable to check for the keys. Say 'cond'
The logic of 'cond' will be

Code: Select all

if  NOT(RefLink.NOTFOUND) AND main_id = lookup_id AND ICONV(dt_record,"D/MDY[2,2,4]") >= ICONV(dt_start,"D/MDY[2,2,4]") AND ICONV(dt_record,"D/MDY[2,2,4]") <= ICONV(dt_end,"D/MDY[2,2,4]") then 'P' else 'N'

in the constraint of the output link, specify

Code: Select all

cond = 'P'

This way only those rows will pass that specify the above condition.

DSguru2B thats works!

Thanks everybody,

Fernando