Lookup Question
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 57
- Joined: Fri Feb 24, 2006 7:44 am
Lookup Question
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.
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.
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".
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".
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Charter Member
- Posts: 57
- Joined: Fri Feb 24, 2006 7:44 am
kumar_s wrote: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
-
- Charter Member
- Posts: 57
- Joined: Fri Feb 24, 2006 7:44 am
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
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
-
- Charter Member
- Posts: 57
- Joined: Fri Feb 24, 2006 7:44 am
Hi,kumar_s wrote: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.
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
-
- Charter Member
- Posts: 57
- Joined: Fri Feb 24, 2006 7:44 am
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,thumsup9 wrote: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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 57
- Joined: Fri Feb 24, 2006 7:44 am
e.g this is my source recordkumar_s wrote: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.
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 .
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.
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.