Lookup Question

[goriparthi]

Hi guys,


i have a scenario in which i have to lookup multiple values

e.g this is my source record

date1 key id

06/10/06 abc 284

and this record will do a lookup on these records

date2 key id

01/10/06 abc 123

05/04/06 abc 234

07/08/06 abc 345

08/22/06 abc 456

now do a lookup on abc and then i want to update 284 to 234 based on the date if date2<= date1 and take the corresponding id and populate 234


Now this i am unable to do in a hash file as it will give only one value for key either max or min depending on the order by clause.

Any thoughts and suggestions will be helpful.

[kumar_s]

Do lookup on the "key" and in constraint part, have your required condition "date2<= date1 ". And in derivation Map the "id" field.
You can avoid hashed file. If you want you, you need to mark both "date2" as well as "key" fields as key. Where in you are actually looking at only "key".

[goriparthi]

Do lookup on the "key" and in constraint part, have your required condition "date2<= date1 ". And in derivation Map the "id" field.
You can avoid hashed file. If you want you, you need to mark both "date2" as well as "key" fields as key. Where in you are actually looking at only "key".

Hi Kumar,

I tried the entire thing what u have said but i didnt use date2 as key
i have to try that

Thanks

[kris007]

From your data, there are more than one instances where date2<=date1. How do you know which value is to overwrite 284(123 or 234). Is there any other condition I am missing out.

[kumar_s]

"date2" is not required to be a key other than "=" operation(Exact Match) .

[goriparthi]

Kumar,

If date2 is given as key, then we don't have any column in source table to lookup in transformer.

lookup functioning i need,

1) check the 'key' column

2) check if date2 < date1

3) If multiple records are found with step2 then get the record with latest date2
4) update ID in source record with the value in lookup hash file for that corresponding date2


issues,

1) source has multiple records with same 'key' column value

2) lookup hash file has multiple records with same 'key' column value


In datastage job when 'key' is given as key column then,

I need each source record to lookup with multiple records in hash file.

But job is performing only one to one lookup

Can u give suggestions

Thanks

[kumar_s]

As said earliar, "date2" cannot be key.
Avoid Hashed file.
Lookup on "Key".
You will get multiple match, have a constraint as "date2<=date1".
Map the id in derivation.

[goriparthi]

As said earliar, "date2" cannot be key.
Avoid Hashed file.
Lookup on "Key".
You will get multiple match, have a constraint as "date2<=date1".
Map the id in derivation.

Hi,


Can u guys say how to get the latest record in db2udb stage which satisfies the date2<=date1 condition because there will be multiple records that satisfies this condition

Thanks

[thumsup9]

assuming date2 for one rec is 10th and other record is 9th and you have date 1 as 8th. In this case you have 2 records greater than 8th. You can select the max(date2) to get the latest from them.

HTH

[goriparthi]

assuming date2 for one rec is 10th and other record is 9th and you have date 1 as 8th. In this case you have 2 records greater than 8th. You can select the max(date2) to get the latest from them.

HTH

Can we do this in a lookup doing a lookup on one key (see in my scenario) and then when it get multiple hits in the lookup can we restrict it to one depending on the date,

i mean if the date is in between 05/04 and 07/08 then i want it to take the 05/04 corresponding id to be updated in my source record.
here there can be multiple records which may have a date less than the date we are comparing but i want the very next date which is less than my source date .

Any suggestions please how to do this in a lookup using a transformer.

Thanks

[ray.wurlod]

You must connect the reference data via either ODBC stage or UV stage - these are the only stage types in server jobs that support multi-row return.

[kumar_s]

You can do this after doing a lookup, and sorting based on the data and using stage variables picking the lates and transforming.
But if yo give with some example, it will be easy to follow up.

[goriparthi]

You can do this after doing a lookup, and sorting based on the data and using stage variables picking the lates and transforming.
But if yo give with some example, it will be easy to follow up.

e.g this is my source record

date1 key id

06/10/06 abc 284

and this record will do a lookup on these records

date2 key id

01/10/06 abc 123

05/04/06 abc 234

07/08/06 abc 345

08/22/06 abc 456

now 06/10 is in between 07/08 and 05/04 so i need the one less than 06/10 that would be 05/04 and the corresponding 234 should be updates in the source id coloumn.here i will have 01/10 also which is less than 06/10 but iwant the next value or the min difference date.This is how my logic should work.

Now if i do a loookup it will just be on key coloumn and it will get 4 hits and its taking only the last value depending on the order in which is sorted.Can you please explain how to do this with this example .

[thumsup9]

Can we do a join here, I am not sure if your source and lookup are both coming from tables on same database, if yes then we can do a join on
Table1 and table2

Select KeyId2 from table2 where date2 =
(Select Max(table2.Date2) From Table1, Table2 where Table1.Date1<Table2.Date2)

You can map this KeyId2 to your target field.

Only works if both tables can be joined.

[kumar_s]

So Sort based on "Key" as well as "date2", but "date2" in reverse order.