Hi all,
I am looking into a file which has a group of numbers, (comma delimited)
123,432,623,7654,234
234,3456,4353
7456,534
I have to do a lookup with all the individual numbers in the group and check if they are in the table,
If atleast one of them is not found in the lookup then I have to reject the entire row.
The issue is that I do not know the maximum number of numbers which can be in a group.
What would be the best approace for this
Lookup in a comma delimited file
Moderators: chulett, rschirm, roy
Lookup in a comma delimited file
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
You can again use the same logic that Ken had suggested few days ago. Load this csv file into a hashed file with just one column containing the concatenation of the individual fields.
For your case:
But you should atleast be able to guesstimate the maximum number of columns that a record can contain. Is it really impossible to approximately guess how much would the size of the record with max. numbers??
Anyways, will let you know if I can think of any other way.
Thanks,
Naveen.
For your case:
Code: Select all
1234326237654234
23434564353
7456534
Anyways, will let you know if I can think of any other way.
Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Ray,
Can you please throw more light on this,
Can you please throw more light on this,
If atleast one of them is not found in the lookup then I have to reject the entire row.
The issue is that I do not know the maximum number of numbers which can be in a group
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Pivot preserving @INROWNUM.
Perform lookup, recording found/not found result.
Vertically pivot, recording "And" of results.
Continue if result still equals TRUE.
Perform lookup, recording found/not found result.
Vertically pivot, recording "And" of results.
Continue if result still equals TRUE.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 130
- Joined: Mon Sep 06, 2004 3:05 am
- Location: Dubai,UAE
Hi narshimha,
If I understand your requirement correctly then
You want to look up these numbers against a database table column and if any of these numbers are present in the column then reject the row.
As suggested, you can pivot the data in the sequential file to get it in this format
123
432
623
7654
234
....
This can be done using the function
Ereplace(DSLink3.InputRecord,',',Char(13):char(10))
Now you can load this data into a Sequential File and then load to a Hash File.
After this a normal lookup can be done against the hash file key column to check whether that value is present in the column or not.
Hope this helps
If I understand your requirement correctly then
You want to look up these numbers against a database table column and if any of these numbers are present in the column then reject the row.
As suggested, you can pivot the data in the sequential file to get it in this format
123
432
623
7654
234
....
This can be done using the function
Ereplace(DSLink3.InputRecord,',',Char(13):char(10))
Now you can load this data into a Sequential File and then load to a Hash File.
After this a normal lookup can be done against the hash file key column to check whether that value is present in the column or not.
Hope this helps
Warm Regards,
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
I guess you are right, but with an addition
I had to remember the group to which the number belonged, and reject the entire row/group. So i had to split them up(remember the group), do the lookups's. Then reject the group which had atleast one number missing. I played around a little with hashed files, to solve my issue. It was fun.
Thanks all
I had to remember the group to which the number belonged, and reject the entire row/group. So i had to split them up(remember the group), do the lookups's. Then reject the group which had atleast one number missing. I played around a little with hashed files, to solve my issue. It was fun.
Thanks all
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.