[SQLITE] Rearranging table

[MrChiliCheese]

Thank you very much for your offer, I'm willing to take it! ;-)

Create my own MainDB to handle things better is a way, that I like.

The only columns that I need are Time_ms, VarName and VarValue. Everything else is just output of the other software that I have to take as it is.

As we are doing it from the scratch now: I'm going to have another DB, also with a column Time_ms. After sorting the first DB (like in the picture in the first) I need to JOIN it with the second one.

This will be written into a file and this will be analyzed manually in Excel!

[jchd]

Fine. Start by downloading the latest freeware release from the SQLite Expert website. This is the best 3rd-party SQLite manager for Windows and it will save you countless hours of work.

If you're have a need to export to Excel, please consider buying the full version: it isn't very expensive, it's a one-time license fee forever and it allows export in various formats (including Excel), printing, automation, etc.

My first question is whether we're sure that DB2 will contain all the times that will appear in DB1 (using your definitions)?

If so, the best is to process DB2 before DB1.

Another question is about the expected volume of data: thousands or hunderd millions of rows.

Same for distinct values for MsgClass: will classes be fixed or unbounded? Is that a short text?

Is MsgNumber a unique integer? Is it correct to use MsgNumber as an integer primary key for a message table?

Are MsgTxt values fixed or variable. If variable will you search partial text there?

My guesses so far: messages are fixed text, have a fixed msgnumb and each has a fixed class which is a short text.

Can you answer every point?

Edited February 6, 2013 by jchd

[MrChiliCheese]

Fine. Start by downloading the latest freeware release from the SQLite Expert website. This is the best 3rd-party SQLite manager for Windows and it will save you countless hours of work.

If you're have a need to export to Excel, please consider buying the full version: it isn't very expensive, it's a one-time license fee forever and it allows export in various formats (including Excel), printing, automation, etc.

I'll do that!

My first question is whether we're sure that DB2 will contain all the times that will appear in DB1 (using your definitions)?

If so, the best is to process DB2 before DB1.

No, that might not be the case. The time is not in the format dd.mm.yy hh:mm:ss. Actually it is a Float! The "times" in DB1 should qual the time in DB2 by +/- xx. (need to define this).

Another question is about the expected volume of data: thousands or hunderd millions of rows.

Same for distinct values for MsgClass: will classes be fixed or unbounded? Is that a short text?

Is MsgNumber a unique integer? Is it correct to use MsgNumber as an integer primary key for a message table?

Are MsgTxt values fixed or variable. If variable will you search partial text there?

About 50.000 maximum in each file.

MsgClass is an Integer

MsgNumber is an Integer, together with the time is unique

MsgText is variable (or the first 1-10 sign are variable). Not searching for text!

My guesses so far: messages are fixed text, have a fixed msgnumb and each has a fixed class which is a short text.

Away from the messages you are right!

Can you answer every point?

[jchd]

I see. Could you please post either publicly here or in MP a good sample of both sources (within the same time range) so that I can have a look at how best you can design an efficient schema? Few hundreds to one thousand entries will do, expecting the sample will cover most cases including missing entries.

Also give one or two samples of timestamp equivalence between your Julian date timestamps (your floats) and their YMD hms.s equivalent.

Finally some hint about +/- xx would be useful. I expect that corresponding entries in both sources will match on the same timescale, besides +/-xx and missing entries.