For loop: Finding matching rows between 2 files

[e5c3d4dant]

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; 

NEXT a 

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.

Thanks very much in advance for your help. 

[chrismarlow]

Hi,

Maybe as an alternative you could do a series of joins & then come up with a formula that scores the match, so the attached does the joins & highlighted where RMINUS1 is set to YES for job2;

Complete script below as I have personal edition, don't know if this helps.

Cheers,

Chris.

jobs_table:
LOAD * INLINE [
JOB ,   SKILL,    RATING
job1,    skill1,    3
job1,    skill2,    4
job1,    skill3,    6
job2,    skill2,   5
job2,    skill3,    2
job2,    skill4,    6
];

target_table:
LOAD * INLINE [
TJOB,    TSKILL,    TRATING
job3,    skill1,    3
job3,    skill2,    4
job3,    skill3,    6
];

left join (jobs_table)
load
	TSKILL AS SKILL,
	TRATING AS RATING,
	'YES' AS EXACT
resident target_table;

left join (jobs_table)
load
	TSKILL AS SKILL,
	TRATING-1 AS RATING,
	'YES' AS RPLUS1
resident target_table;

left join (jobs_table)
load
	TSKILL AS SKILL,
	TRATING+1 AS RATING,
	'YES' AS RMINUS1
resident target_table;

[e5c3d4dant]

That's fantastic! Thanks very much. 

The only thing I need to do now is put RPLUS1 and RMINUS1 in the same column. 

I've tried a few things:

1. concatenate

2. having a criterion the Left Join instruction to say TRATING+1 or TRATING-1

3. Joining the records in a new table at a later stage. 

However I can't get any to work. 

Thanks again for the solution to the main issue. It seems to work perfectly! 

[chrismarlow]

Hi,

Glad it helped. Try the following at the end of the script;

jobs_table_final:
NoConcatenate
Load
	If(RPLUS1='Yes' Or RMINUS1='YES','YES',Null()) AS NEARMISS,
	*
Resident jobs_table;

DROP TABLE jobs_table;

Hope that helps.

Cheers,

Chris.

[e5c3d4dant]

Fantastic! 

Thanks very much for your help and prompt reply.