Lookup in a comma delimited file

narasimha · Post by **narasimha** » Mon Mar 13, 2006 11:22 am

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

I_Server_Whale · Post by **I_Server_Whale** » Mon Mar 13, 2006 11:31 am

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:

Code: Select all


1234326237654234 
23434564353 
7456534

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.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 13, 2006 11:31 am

In a word, Pivot

narasimha · Post by **narasimha** » Mon Mar 13, 2006 12:02 pm

Ray,

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

ray.wurlod · Post by **ray.wurlod** » Mon Mar 13, 2006 12:10 pm

Pivot preserving @INROWNUM.
Perform lookup, recording found/not found result.
Vertically pivot, recording "And" of results.
Continue if result still equals TRUE.

Sunshine2323 · Post by **Sunshine2323** » Tue Mar 14, 2006 1:19 am

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

narasimha · Post by **narasimha** » Wed Mar 15, 2006 2:46 pm

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