Databases
StackOverflow2013
Postsdbo
POTransform raw data into relational data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTransform raw data into relational data
    primarykey
    data
    text
    <h2>Intro</h2> <p>I've been given a messy excel dump straight into a table. Now I need to turn that mess into something useful. The dump has duplicates and inconsistencies... good times!</p> <p>I've been striking out on every approach so far :( - Hope you can help me out.</p> <p>Given this example data set:</p> <pre><code>ExcelDump +----+------+------+------+ | ID | Col1 | Col2 | Col3 | +----+------+------+------+ | 1 | | | C | | 1 | | B | C | | 1 | A | B | D | | 1 | E | B | C | | 2 | A | B | C | | 2 | A | B | C | | 3 | A | B | C | | 3 | A | B | F | | 4 | A | B | C | | 4 | G | B | C | +----+------+------+------+ </code></pre> <p>One possible result could be:</p> <pre><code>OutputTable +----+------+------+------+ | ID | Col1 | Col2 | Col3 | +----+------+------+------+ | 1 | A | B | C | | 2 | A | B | C | | 3 | A | B | C | | 4 | A | B | C | +----+------+------+------+ </code></pre> <p>Nice and neat. Unique ID key and data merged together in a way that makes sense.</p> <h2>How to choose which data is correct?</h2> <p>You've probably noticed that another possible result could be:</p> <pre><code>+----+------+------+------+ | ID | Col1 | Col2 | Col3 | +----+------+------+------+ | 1 | E | B | C | | 2 | A | B | C | | 3 | A | B | F | | 4 | G | B | C | +----+------+------+------+ </code></pre> <p>This is where it gets complicated. I want to be able to choose the set that makes the most sense based on some conditions I can manipulate.</p> <p>For instance I want to setup a condition that says: <strong>"Choose the most (non-null) common value, if no most common found take the <em>first</em> value found that is not null."</strong> This condition should be applied to the selection of grouped by IDs. The result of that condition would be:</p> <pre><code>+----+------+------+------+ | ID | Col1 | Col2 | Col3 | +----+------+------+------+ | 1 | A | B | C | | 2 | A | B | C | | 3 | A | B | C | | 4 | A | B | C | +----+------+------+------+ </code></pre> <p>If I later find out that that assumption was wrong and it instead should be: <strong>"Choose the most (non-null) common value, if no most common found take the <em>last</em> value found that is not null."</strong></p> <pre><code>+----+------+------+------+ | ID | Col1 | Col2 | Col3 | +----+------+------+------+ | 1 | E | B | C | | 2 | A | B | C | | 3 | A | B | F | | 4 | G | B | C | +----+------+------+------+ </code></pre> <p>So basically I want to select values based a set of conditions on each group of IDs.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSnæbjørn
    1. USSnæbjørn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTransform raw data into relational data
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow did you choose the value `B` of `Col1` for `ID = 3` instead of `A`?
      singulars
      1. POTransform raw data into relational data
      1. USLamak
    2. CO@Lamak also why is `Col1` `A` for `ID = 1` instead of `NULL`
      singulars
      1. POTransform raw data into relational data
      1. USConrad Frix
    3. CO@ConradFrix - I just assumed that op somehow was grouping by `Id` and for `ID = 1` there was just one row that has a value for a column different of `NULL` or empty. But is not the case for `ID = 3`
      singulars
      1. POTransform raw data into relational data
      1. USLamak
 

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