I have 2 tables. I want to iterate through table 1 to find matching, or near matching rows in table 2.
The first table is jobs_table: a list of jobs alongside associated skills and ratings.
JOB SKILL RATING
job1 skill1 3
job1 skill2 4
job1 skill3 6
job2 skill2 5
job2 skill3 2
job2 skill4 6
The second table is target_table. This is in the same format and contains a single job.
TJOB TSKILL TRATING
job3 skill1 3
job3 skill2 4
job3 skill3 6
I want to test every row in the target_table to see if the same or similar rows appear in the jobs_table for the SKILL and RATING fields.
I've used Autonumber to assign a unique ID agains each row of jobs_table. I've then iterated each item using:
For each i in FieldValueList('ID')
LOAD '$(i)' as NEWFIELD AutoGenerate 1;
However I need to add a criterion where a new line is added to the new AutoGenerate table only if the SKILL & RATING = TSKILL & RATING. I also want to specify which field to add to the new table.
I might be wrong but I think that I will need to iterate through the rows rather than simply use filters. This is because exact matches are just the first stage of what I want to do, that is, other jobs that have the same skill with exactly the same rating. After that I'd like to find near matches, i.e. jobs which have skills with a rating plus or minus 1 of the target. I will then start to rank the matches. Having said that, I don't think I need help with the full task. I'm sure that once I can see how to pick out fields, i'll be able to do the rest.