Efficiency of Lookups

[jimrward]

I am still on the learning curve so any advice is welcome
I have a raw data set of 120k rows of data, from which I have nearly 20 routines constructed which work fine
The raw data has many targetID's with multiple lines for each targetID
some of the TargetID's are historical as far as my reporting goes so I would like to reference these against another table Report, which will have 2 columns TargetID, Current where Current is Y or N
In sql it would be on the lines of
select from master where TargetId IN (select TargetId from Report where Current="Y")

approach 1 lookup each TargetID in Report which would be 120k references which if any lookup is not very efficient is going to take a lifetime, filter on this then use this dataset as my start point for the rest of my routines
approach 2 keep my current modules and as a final step do the lookup which would be a vastly reduced lookup and should breeze thru it

I am leaning towards #2 above, unless there is an efficient method for #1

 

[GraH]

In PQ you can write it as List.Contains( List, Item) in a filter which is List.Select or Table.SelectRows. or filter the reference table first, then do a inner.join through a merge tables.
Trial and error to see which approach runs fastest.

 

[jimrward]

@GraH thanks for that
I reached a point with a small results set thru filter and group of say 30 rows and then the lookup using positionof against another list of 20 rows was strange sometimes slow sometimes seconds even using list.buffer I tried contains and a slight improvement PQ seems to have its own way of doing things