How can I reorganise data in a spreadsheet based on a common ID

[TheOverlord]

Hi everyone! I am a relatively new convert to LibreOffice having spent decades using THAT OTHER suite. As a translator, I often need to organise glossaries from various sources (e.g. the EU’s IATE programme) so I can use them in my favourite tools.
I now have a LibreOffice Calc file containing 1346 rows in 5 columns. I’d like the data to be formatted differently. The first column contains a multiple digit number (E_ID). If the number occurs more than once, it designates one data record, which is then in German or English, depending on the content of column B (L_CODE), which is either “en” or “de”. Ideally, the data should be sorted such that within a series designated by one E_ID, the contents of each row are organised somehow by the L_CODE. If there are several “en” or “de” within an L_CODE series (synonyms), they should go in subsequent columns. For example:
|64193|de|Druckluftpistole|Term|criminal law;justice;international law;defence|
|64193|de|Luftdruckpistole|Term|criminal law;justice;international law;defence|
|64193|de|Luftpistole|Term|criminal law;justice;international law;defence|
|64193|en|compressed air gun|Term|criminal law;justice;international law;defence|
|64193|en|air pistol|Term|criminal law;justice;international law;defence|
To make this a useable glossary I need:
64193|Term|Druckluftpistole|Luftdruckpistole|Luftpistole|compressed air gun|air pistol|criminal law;justice;international law;defence
The problem being that there may be any number of entries with the same ID, from two to ten or more.
Does anyone know how to run this in such a way that I can adapt it possibly later for other patterns?
Here is the full ODS
IATE_export copy.ods (44.5 KB)

[Wanderer]

@TheOverlord

they should go in subsequent columns

I don’t think this is a good idea as you would have different numbers of columns then. I would “collect” synonyms in one column with a separator.
.
I would use your current sheet as Sheet INPUT and reference from a second sheet JOIN:
A: =IF(INPUT.A1<>""; INPUT.A1; "")
B: =IF(INPUT.B1<>""; INPUT.B1; "")
and drag this down

only C1: A: =IF(INPUT.C1<>""; INPUT.C1; "")
C2: = IF( AND( INPUT.A2=INPUT.A1; INPUT.B2=INPUT.B1; INPUT.C2<>"" ) ;C1&"/"&C2; C2)
The formula looks in the previous row and concatentes values with a separator, if id and language is identical.
Drag this down and check result.
.
Now you need a marker column to show what rows are “final” rows, so look in next row:
X2: = IF( AND( INPUT.A2=INPUT.A3; INPUT.B2=INPUT.B3; INPUT.C2<>"" ) ;"FINAL"; "continued")
Drag this down and check result.
.
For manual use you can set a filter for column X to show only “FINAL” rows. Otherwise I would use also abfilter, but sendvthe output of the filter to a third sheet named DATA and use this.
.
(It is possible to use Base for this, but - as usual - this was not part of the question.)

@TheOverlord

run this in such a way that I can adapt it possibly later for other patterns?

Will depend heavily on the “patterns” (and on your capabilities), so no clue…

[Villeroy]

How exactly would you do that in Excel? The column labels look as if you exported these data from a database. Why export textual database data into an arithmetic calculator anyway?
Firebird_LIST_Function.odb (47.0 KB)
Open the embedded form and report.

[TheOverlord]

Hi Villeroy
The background is a CSV download of terminology from the EU databases. This is referred to as IATE. Unfortunately, if I open the CSV in any spreadsheet, it understandably just puts each line of the CSV in a separate row of the spreadsheet. I was never good enough in Excel to do clever things like that, but I’m sure that would be possible. So I assume it’s possible in LO.
I’ve had a look at your linked ODB. What I would then need is a way of implementing something like
if ID = xyz and L_CODE = en, do something
if ID = xyz and L_CODE = de, do something that puts them together in one row.

[TheOverlord]

Hi Wanderer
Many thanks for the input! This looks promising.
The idea of collecting synonyms would be fine.
Unfortunately, my skills with spreadsheets are on the same level as my ability to build a nuclear power station. What I would need is step-by-step instructions on where to put what. You mean set up two separate sheets named INPUT and JOIN?
When you say “drag this down”, I had used that function in small lists in Excel. Click square mark at bottom right and pull down. Is there a way to drag down to the bottom row (1346) other than “physically” dragging?

[Villeroy]

If you showed us, how you did that trick in Excel …

[CRDF]

@TheOverlord

that puts them together in one row

Like this way for #116082?

[Lupp]

@TheOverlord

I now have a LibreOffice Calc file containing 1346 rows ...

What I actually need to know:
Can you assure a maximum number of rows which will also cover possible “enrichment” in the future?

[Wanderer]

@TheOverlord

Is there a way to drag down to the bottom row

Double-click (should also wirk in Excel):

LibreOffice Calc: How to auto-fill a row with double click on handler?

According to https://help.libreoffice.org/Calc/Automatically_Calculating_Series: You can double-click the fill handle to automatically fill all empty columns of the current data block. However, there is no similar feature for rows. Instead, to fill many cells, do the following: Go to the cell to autofill from. Press Ctrl+Shift+End to select the cells to fill. Fill right by going to Sheet → Fill Cells → Right (Alt+s, i, r on my machine). Tools → Customize can be used to set up a key comb…

But I usually “develop” this kind of sheets in the first 5 or 10 rows and then mostly use copy/paste as I can mark with shift-ctrl-End to the end of my table (and fine-tune via shift+Left Arrow etc. , if needed)

[Villeroy]

@TheOverlord

if ID = xyz and L_CODE = en, do something
if ID = xyz and L_CODE = de, do something that puts them together in one row.

Same form and report grouped by E_ID and L_ID, so you get the concatenated descriptions separated by item ID and language.
Firebird_LIST_Function2.odb (46.9 KB)

[CRDF]

Selection via Filter Table
 
E_ID

 
E_ID + L_CODE

[Villeroy]

@CRDF

Selection via Filter Table

Firebird_LIST_Function3.odb (56.5 KB)

Like this?

[CRDF]

@Villeroy

Like this?

No links between Forms.
User

1. inserts the parameters and save them (the record);
2. >> update the Glossary.

SQL for the concatenation (in 2 queries):

SELECT DISTINCT "TBL"."E_ID" || ASCII_CHAR(10) || LIST( '(' || "TBL"."L_CODE" || ' - ' || "TBL"."T_TYPE" || ') ' || "TBL"."T_TERM", ASCII_CHAR(10)) "Term",
LIST(DISTINCT "TBL"."E_DOMAINS", ASCII_CHAR(10)) "Domains"
FROM "TBL" 
JOIN "PARAMETROS" "P" ON "TBL"."E_ID" = "P"."E_ID"
AND "TBL"."L_CODE" = "P"."L_CODE"
WHERE "P"."FLAG" IS TRUE
GROUP BY "TBL"."E_ID"

SELECT "Term" || ASCII_CHAR( 10 ) || ASCII_CHAR( 10 ) || "Domains" "Glossario" FROM "qMultilinhas_Lingua"

[TheOverlord]

Sorry for the delay, Villeroy and all others…it’s been a busy week. To answer the one question: I have cancelled my MS Office subscription so I haven’t got access to Excel any more. However, it was the “Text to columns” function. I only used it a couple of times.

[Wanderer]

@TheOverlord

Text to columns

is also available in LibreOffice, but I’m not aware of an option to join rows…

[Villeroy]

Firebird_LIST_Function4.odb (56.7 KB)

• Copy the query icon Terms_Domains2Sheet.
• Paste into spreadsheet.
• Call Data>“Text to columns” with column separator | (pipe symbol).

[Villeroy]

I forgot: How to get csv data into a Firebird database?
Import into Calc.
Copy the cell range.
In the Base window, select the table icon “TBL” and paste.
In the upcoming dialog, the table name should be set together with “Append data”.