1

I have a LibreOffice Calc spreadsheet which contains two sets of time series data e.g.

|Date-A    |A  | | | |Date-B    |B  |
|2020-01-01|101| | | |2020-01-01|201|
|2020-01-02|102| | | |2020-01-02|202|
|2020-01-03|103| | | |2020-01-03|203|
|2020-01-04|104| | | |2020-01-04|204|
|2020-01-05|105| | | |2020-01-05|205|
|2020-01-06|106| | | |2020-01-08|206|
|2020-01-07|107| | | |2020-01-09|207|
|2020-01-08|108| | | |2020-01-10|208|
|2020-01-09|109| | | |2020-01-11|209|

Notice that some dates are missing for (e.g. 2021-01-06 and 2021-01-07 for Date-B)

I want to unify these sets of data using only rows for which both Date-A and Date-B are present. In this case the result would be

|Date      |A  |B  |
|2020-01-01|101|201|
|2020-01-02|102|202|
|2020-01-03|103|203|
|2020-01-04|104|204|
|2020-01-05|105|205|
|2020-01-08|108|206|
|2020-01-09|109|207|

Observe that 2020-01-06 and 2021-01-07 are omitted, and the correct A and B values are present for all dates which exist in both data sets.

Can I do this in LibreOffice?

1 Answer 1

1
  1. Create a new LibreOffice Base database (embedded hsqldb);
  2. Create two tables (A, B) that have the same structure as your data columns;
  3. In your spreadsheet, select and copy data columns Date-A and A;
  4. Paste those two columns in Base into Table A (in table view, select table A and hit CTRL+V)
  5. Repeat steps 3 and 4 for columns of B data (Date and Data);
  6. Create a new Query in SQL mode:
SELECT "Date-A" AS "Date", "A", "B" FROM "A" JOIN "B" ON "A"."Date-A" = "B"."Date-B";

Result:

Result

Additional Remarks: Don't try to create a new db based on your spreadsheet directly. Those databases don't support JOINs (AFAIK), you will receive strange error messages if you try... (as i did first)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .