Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Apologies that I'm not able to provide any sample script, but I have no idea where to begin with this and can't find anything similar in the forums.
I would like to do the following in the script, as I believe it is more effective...
I have a very large table called TABLE_INFO with the following key fields (there are many other fields in addition to these):
Qual_pred | Is_A | Is_B | Is_C | Prior_AB | Prior_C |
---|---|---|---|---|---|
Spanish | 1 | 28 | 34 | ||
Spanish | 1 | 33 | 38 | ||
Spanish | 1 | 20 | 27 | ||
English | 1 | 43 | 54 | ||
English | 1 | 29 | 27 |
I then have another table (TABLE_NEW) which I would like to join to TABLE_INFO, with the following fields:
Qual_pred | Min | Max | Outcome |
---|---|---|---|
Spanish | 16 | 20 | 33 |
Spanish | 21 | 25 | 37 |
Spanish | 26 | 30 | 40 |
Spanish | 31 | 35 | 42 |
English | 18 | 21 | 28 |
English | 22 | 25 | 36 |
English | 26 | 29 | 38 |
I need to do the following for every row in TABLE_INFO.
1) Look at the 'Qual_pred'.
2) If it is 'Is_A' or 'Is_B', use value 'Prior_AB' / If it is 'Is_C', use value 'Prior_C'
3) Look in TABLE_NEW and find matching 'Qual_pred'
4) If value 'Prior_AB' or 'Prior_C' (depending on step 2), falls between 'Min' and 'Max', return 'Outcome' value to TABLE_INFO
I would hope to end up with the following:
Qual_pred | Is_A | Is_B | Is_C | Prior_AB | Prior_C | Outcome |
---|---|---|---|---|---|---|
Spanish | 1 | 28 | 34 | 40 | ||
Spanish | 1 | 33 | 38 | 42 | ||
Spanish | 1 | 20 | 27 | 33 | ||
English | 1 | 43 | 23 | 36 | ||
French | 1 | 17 | 29 |
Any help appreciated - even just a hint as to what direction I should go in!
Many thanks in advance,
Jess
Paul, that helps enormously - thank you so much!
I now have a single table of data, with the Outcomes matched in correctly.
For some reason I'm still getting duplicates in my worksheet table, showing nulls for outcome, e.g:
Qual_pred | Is_A | Is_B | Is_C | Prior_AB | Prior_C | Outcome |
---|---|---|---|---|---|---|
Spanish | 1 | 28 | 34 | 40 | ||
Spanish | 1 | 28 | 34 | - | ||
Spanish | 1 | 33 | 38 | 42 | ||
Spanish | 1 | 33 | 38 | - | ||
Spanish | 1 | 20 | 27 | 33 | ||
Spanish | 1 | 20 | 27 | - | ||
English | 1 | 43 | 23 | 36 | ||
English | 1 | 43 | 23 | - |
But I'm guessing that could be down to a fault somewhere else in my script... Hopefully it won't affect my calculations.
Thanks once again for your help.
Best wishes,
Jess