converting a flat table to a join table

[human]

I have a table with 160K rows. For the purposes here, say each row has a jobID, a trackID (a music track), and a status ID (1 = clear, 2 = sold, etc). I am trying to convert that table to a join table which would associate each unique jobID to a unique trackID (I need to do this in order to exchange data with an external system that expects a join table, and over which I don't really have any control). The problem I'm running into is that the "flat" table can contain a row where the jobID and trackID match another row, but the status is different between the two rows. In that case, whichever row has the higher status is the one that should be written to the join table. Seems simple, but I can't figure out how to implement it. I've already converted all job ID's from the "flat" table to a jobs table, and there is a job_file join table connected via the jobID to the jobs table. Starting with an empty job_file DB, I'm looping through the jobs table. For each jobID, I go back to the "flat" DB layout and search for all tracks with the same jobID. Say I do this and I get 5 rows. However two of those rows are the same, except one has a higher status than another - how am I supposed to keep track of that and know which one to write to the join table? I thought maybe for each jobID in the loop iteration, I'd keep a list of trackID's and then check that list for duplicates - but what I would really need is an array that would store the trackID and its status so I could compare them and know which one to write.

I'm lost in a maze of loops and layouts.

[comment]

45 minutes ago, human said:

I am trying to convert that table to a join table which would associate each unique jobID to a unique trackID

If I follow your description correctly, your table IS the join table. You just need to create the two parent tables, Jobs and Tracks - which can be done simply by importing the existing data, while validating the respective target fields as Unique, Validate always.

 

45 minutes ago, human said:

The problem I'm running into is that the "flat" table can contain a row where the jobID and trackID match another row, but the status is different between the two rows. In that case, whichever row has the higher status is the one that should be written to the join table. Seems simple, but I can't figure out how to implement it.

Maybe like this:

1. Define a calculation field (result is Text) = 

jobID & "|" & trackID 

2. Find duplicates in this field and sort them by the calculation field and by status, descending.

3. Loop over the groups, using the Fast Summaries method, and omit the first record in each group*. Once you've done that, you can delete all remaining records in the found set. Make sure you have a backup before you try this. Or switch to the omitted set and import it to your final table.

--
(*) You seem to be saying that there can be at most 2 records with the same combination of jobID and trackID, and that these will have a different status. In such case, you could simply omit every 2nd record, after finding and sorting as per above.

 

Edited September 30, 2023 by comment

[comment]

Another variation you could try:

After defining the calculation field, show all records and sort them by status, descending. Then import them into the final table, while validating the target field of the calc as Unique, Validate always. 

 

[human]

I found that the idea of making a calculation field jobID & "|" & trackID and searching for duplicates on that, then looping through the duplicates to find the highest value of "status", is a bit complicated to program, but it works, so I'm sticking with that. Thanks!

BTW I'm not deleting anything - the two tables have different uses. The script that converts to the join table (which is actually an ODBC connection to a mysql server) will run on a schedule.

Thanks again!