Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Assuming that</p> <ul> <li>there is no ID with more than 2 alternatives</li> <li>the alternative IDs are in the first columns of the "other" files</li> <li>you only want the "x" to appear in col7 when there is no match, not in col8 if there is only one match</li> <li>you can name one character (<code>DELIM</code>) that is no <code>&lt;TAB&gt;</code> and will <strong>never</strong> appear in any <em>altenative</em> ID</li> <li>all files have <em>exactly</em> <strong>one</strong> header line</li> </ul> <p>the following solution works for me</p> <pre class="lang-sh prettyprint-override"><code>$ cat file1 </code></pre> <pre class="lang-none prettyprint-override"><code>ID col2 col3 col4 col5 col6 a a x 43 5 54 b f 4 44 9 51 c h k 90 0 52 </code></pre> <pre class="lang-sh prettyprint-override"><code>$ cat file2a </code></pre> <pre class="lang-none prettyprint-override"><code>AltID col2 col3 ID A1 some thing a B1 more stuff b </code></pre> <pre class="lang-sh prettyprint-override"><code>$ cat file2b </code></pre> <pre class="lang-none prettyprint-override"><code>AltID col2 col3 ID A2 even more a D1 random stuff d </code></pre> <pre class="lang-sh prettyprint-override"><code>$ cat test.awk </code></pre> <pre class="lang-sh prettyprint-override"><code>BEGIN { FS = "\t" OFS = FS DELIM = ";" MISSING = "x" } FILENAME != "file1" { if (FNR != 1) { alt[$4] = (alt[$4] ? alt[$4] "" DELIM : "") "" $1 } next } FNR == 1 { $7 = "AltID" $8 = "AltID2" print next } { idcount = split(alt[$1], ids, DELIM) $7 = (idcount ? ids[1] : MISSING) } idcount &gt; 1 { $8 = ids[2] } 1 </code></pre> <pre class="lang-sh prettyprint-override"><code>$ awk -f test.awk file2* file1 </code></pre> <pre class="lang-none prettyprint-override"><code>ID col2 col3 col4 col5 col6 AltID AltID2 a a x 43 5 54 A1 A2 b f 4 44 9 51 B1 c h k 90 0 52 x </code></pre> <p>Feel free to ask for adjustements or explanations in comments and I'll update my answer accordingly.</p>
    singulars
    1. This table or related slice is empty.
    1. POSearch and Append
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmschilli
    1. USmschilli
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSearch and Append
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi sg-lecram Thanks a lot for the script. It works almost flawlessly. I'm having an issue right now where it will put "x" on the second column occasionally and the ID on the first. I also noticed that at some points it prints the ID twice, so I end up with three columns at certain points.
      singulars
      1. PO
      1. USuser2647734
    2. COActually, ignore that. I figured out the problem. I had line breaks inserted by excel into the txt file. Got rid of them and it worked like a charm. Thanks a lot!
      singulars
      1. PO
      1. USuser2647734
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload