[Solved] Analyze which suppliers are in two different tables

[Albireo]

Assume that there are a total of 12 suppliers, like this. (there is many more in the real case.

• Supplier_ID
  Supp1
  Supp2
  Supp3
  Supp4
  Supp5
  Supp6
  Supp7
  Supp8
  Supp9
  Supp10
  Supp11
  Supp12

Suppose there are two tables similar to these (Two PivotTables are shown on the right)

Dual tables.png (31.72 KiB) Viewed 1480 times

My wish is to compile which suppliers are in both tables ( table1 and table2 )
Which would give the following result .:

• Supplier_ID1
  Supp1
  Supp3
  Supp4
  Supp6
  Supp9

It is possible with a formula.? How?

Attaches a test file with data (Supplier_ID1 is manually entered)

Dual tables.ods

(21.41 KiB) Downloaded 50 times

[UnklDonald418]

As the name Calc implies, this software was designed primarily to store and do calculations on numeric data. While a Calc spreadsheet can be (mis)used as a simple database there limits to that functionality.
A relatively simple database query would deliver the results you desire but there is a definite learning curve when it comes to relational database design and implementation.
https://wiki.documentfoundation.org/ima ... torial.pdf

[Albireo]

Thanks! (especially for the attached file about Base)

I understand the simplicity of a database at e.g. a presentation of some information.
But gets stuck when the basis comes from CSV files. (in this case two files).

I have no idea how to "automatically" create databases from two CSV files. (maybe my bottleneck?)
I can create a spreadsheet in LibreOffice / OpenOffice using a script - but not a database.

Have you any idea?

- . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ . - . _ .

Wrote some (quite complicated formulas) and almost achieved my wish.

[Villeroy]

SInce many years I use Base to import csv files. . However, it requires a lot more configuration work. Once it has been configured there is no more import dialog, no duplicate records, no invalid types, no copy-around of values and formulas.

[Albireo]

Excuse me!
I sent the message before it was finished.
Here comes the file with the formulas.

Dual tables and tabs - web.ods

(20.27 KiB) Downloaded 49 times

One limitation is that a new spreadsheet must be created for each occasion.
(e.g. the formulas become wonky if the lines are copy/deleted in the 1_Source and 2_Source tabs)

Another problem is being able to present the result without rows that have no content.
The output tab from the file above shows the following .:

Dual tables and tabs - web.ods

(20.27 KiB) Downloaded 49 times

Column B contains all interesting suppliers (not many). In tab A, there may be 40-50 different ones, which means that the table could be reduced - but I don't know what such a formula could look like.

[Villeroy]

I can not understand why virtually everybody tries to use spreadsheets for this.

[Albireo]

Offfopic.

I can not understand why virtually everybody tries to use spreadsheets for this.

• One reason is probably that it is so difficult/complicated to understand how to "get started" with Base.
• Another thing could be that it is so easy to copy / move / delete many records in a spreadsheet. Sorting can also work easily, with some limitations.
• A third thing that can also be mentioned is the ease of creating tabs - easy to keep a result together in one file.
• A fourth thought - There are not many companies (in Sweden?) that can be contacted to create a solution in OO/LO.
  This means that if I want a solution in Calc/Base, I have to find it myself. This means that the more I use Calc - the less I use Base.
• A few years ago I tried to create a solution in Base. Asked questions in various forums - but Base was "dead". - got stuck and had to give up.
  Now I don't know if Base has developed much in recent years.

Most guided tours are based on opening Base and creating a table directly in Base and showing some summary - So far no problems.

But in many cases there is existing data to be managed and then it can be significantly more difficult (that's how I experience it)
What happens to duplicates? / empty lines?
Is 1234 (as text) the same as 1234 (as number)?
Will 0001 remain 0001 or just 1? etc.

I have several projects that would be best solved with a database.
But don't know how some details could be resolved or where I can turn and reason about structures
Much is about some type of API - input/output from e.g. Base

OnTopic
- . _ . - - . _ . - - . _ . - - . _ . - - . _ . - - . _ . - - . _ . - - . _ . - - . _ . - - . _ . - - . _ . - - . _ . -

This specific compilation is done approximately twice a year.
The basis for this consideration is two CSV files that contain approx. 6000 lines and two fields (article number and quantity),
most of them are unique but not all. Product group, supplier, VAT, price, etc. are added to each article (row)
Now I got two CSV-files with information.

From these two CSV-files, a well-defined compilation must be made.
It's probably 10-15 moments on each file, before I manage to make a compilation.
Wanted to reduce the number of steps and automate the process a little more - but it seems a bit difficult.
With base?? - Create two tables but - maybe the duplicates must be removed before? - make relations between this two in some way - and so on...
Maybe this must be done every time the compilation is made?
I have no idea how to begin with base.

With Calc I reach the goal, even if it still requires many manual steps.

I have succeeded in picking out all unique suppliers with formulas in Calc.
But if it's smoother than creating two PivotTables, copying the name of all suppliers and manually comparing the result between the two files, I don't know yet.

[UnklDonald418]

There is an old adage, "to a man with a hammer the whole world looks like a nail". To many, a spreadsheet appears to be an intuitive tool like a hammer. But using a spreadsheet for a database is often like driving a screw with a hammer. It might work but the results can be troublesome, thus many of the questions on this Calc forum.
Granted, learning to use a screwdriver is less difficult than learning to design a database but if your goal is simplifying the preparation and analysis of your data I believe a well designed database could do that with importing the data the only step required. Villeroy indicated that he can even skip the process of importing the CSV data into a database, so if you want to try that ask the question on the Base forum.
Your data set doesn't appear to be terribly complex, and since we've been touting the advantages of a database I put together a small database to demonstrate some of the things it could do with your data.

The basis for this consideration is two CSV files that contain approx. 6000 lines and two fields (article number and quantity)

I used the data you provided in your most recent speadsheet, but since you didn't provide part numbers I added some and imported them with the quantitues from the spreadsheet into Table1 and Table2. In addition to PartNumber and Quantity there is a field named ID, a Primary Key that was added during the import process. That is an index used by the database engine and is of no concern at this point.
Since you were concerned about blank rows I also included a few of those in the imported data.
You didn't mention how

Product group, supplier, VAT, price, etc. are added to each article (row)

so I added a table PriceList where I imported some of those details from your spreadsheet.
There is a FILTER table that is used by one of the forms and a View which is a query stored in the backend, so that to Base it appears to be another Table.
There are several queries which is where the magic happens, similar to formulas on a spreadsheet. They link the tables together, perform calculations and sorting. The queries are written in SQL, the language of databases. Not a terrible complex language but there is a learning curve. There are free online tutorials/courses on learning the basics of SQL and several experts that regularly visit the Base forum and help with .
There are 2 Forms that demonstrates displaying the data in a few ways.
In use, the CSV data would be imported into Table1 and Table2. That can be done with cut and paste and a wizard will help you with that, similar to the one you use to import the CSV into your spreadsheet.
If Product group, supplier, VAT, price, etc. are new each time you would need to import that also but if it is static then this step could be skipped after that table is first populated.
That's it. Open the Forms to see results.

[Villeroy]

Offfopic.

I can not understand why virtually everybody tries to use spreadsheets for this.

• One reason is probably that it is so difficult/complicated to understand how to "get started" with Base.
• Another thing could be that it is so easy to copy / move / delete many records in a spreadsheet. Sorting can also work easily, with some limitations.

This versatility makes a spreadsheet an expert tool, a quick scratch pad for users who precisely know what they are doing, what could go wrong and how to fix things when moving around cells having values or formulas referencing absolute references or relative ones.
Sorting a database's row set by one or many columns is a no brainer because it is technically impossible to destroy the row set or any reference.

[*]A third thing that can also be mentioned is the ease of creating tabs - easy to keep a result together in one file.

In other words: User interface, data representation, calculation and data storage are not strictly separated from each other which makes spreadsheets extremely difficult to use safely.

[*]A fourth thought - There are not many companies (in Sweden?) that can be contacted to create a solution in OO/LO.

Base can be connected to existing databases of any type supporting either ODBC or JDBC. A database solution is just a database solution and "Base" is a tiny (and overly simplistic) set of tools to access some database from this office suite.
Why don't people use existing "Excel solutions"? In 20 years I have not seen a single solution that works for anybody else than the creator of that specific "solution". This way there are millions of isolated "Excel solutions" around the globe. On this forum I've seen people who learned an extinct computer language (Basic) instead of SQL and after months and months chewing hundreds of lines of lousy code they ended up with something extremely ugly and error prone, not even close to a simple (imperfect but good enough) solution made with Base.

[*]A few years ago I tried to create a solution in Base. Asked questions in various forums - but Base was "dead". - got stuck and had to give up.
Now I don't know if Base has developed much in recent years.[/list]
Most guided tours are based on opening Base and creating a table directly in Base and showing some summary - So far no problems.

This is completely misleading. You can not learn database development by means of "guided tours" through a tiny addition of an office suite. Anybody who is familiar with any kind of relational database will find quick ways to plug a Base document to his/her existing database and add some meaningful user interface to the database using the little toolkit provided by this office suite. At the same time he/she may use the same database with some accounting software, customer relations, warehousing solution or with a web site. There is no such thing as a "Base database" until you use Base to create a new database from scratch. This special type of "embedded database" can't be used outside the office suite until you get it out of the cage.

But in many cases there is existing data to be managed and then it can be significantly more difficult (that's how I experience it)
What happens to duplicates? / empty lines?
Is 1234 (as text) the same as 1234 (as number)?
Will 0001 remain 0001 or just 1? etc.

I'm 99% sure that the csv data you try to import into Calc had been exported from some database. Having imported database rows into an arithmetic calculator you feel discontent with the calculator's database functionality. In a database you have to define the column types before you append any data to the table. You are obliged to be the master of your database data before you add the first record. If you do not want any duplicates, define what a duplicate is and the database will reject storing duplicates. If you define completeness, completeness will be enforced. A data row is unique and complete with consistent data types or it will not be saved. This makes everything a lot easier to use, but you have to do all the work before storing the first row of data.

I have several projects that would be best solved with a database.
But don't know how some details could be resolved or where I can turn and reason about structures

This is why database development is a well paid profession. However, for what most people try to do on sheets so desperately, there will always be a simple database solution which takes some hours of design work but works for many users easily and reliably for decades.

Much is about some type of API - input/output from e.g. Base

I always try to keep my little "Base solutions" free of macro code. I add little amounts of macro code when my input forms are ready to use but need a little streamlining in usability (save some annoying extra clicks). My little database on the job is used by a small group of users with very little computer literacy. Since 12 years it collects data with no errors, no crashes, no problems with file formats (there are none), no problems with "formatting", no problems with lost references to linked files or dragged-around formulas. You open a form, enter something, click OK and if something is missing, duplicate or otherwise inconsistent you get an error message without any incomplete, duplicate or inconsistent data being stored. EIther you correct your input or cancel the form.
Importing csv? No problem at all, even if Base does not support this directly. I click a button, a file picker dialog starts in the right directory with the right file filter set, you pick your csv file and it will be imported properly with no duplicates nor inconsistencies. Without macro: put the csv to the right place before opening the Base document, open the Base document, copy a prepared view icon onto the target table's icon. All the required type conversion, column order, sort order, duplicate filtering is defined in the view.

[Albireo]

What a commitment - thank you!

First the concrete questions
1a) Did I understand correctly that my main question -
"Is it possible to analyze suppliers from two tables in OO/LO Calc"
is impossible to solve?

1b) It is also difficult to write a formula that points to a cell Create a formula that points to a cell name (eg A4) (maybe impossible?)

Then to structure and handling of data
I agree with most of the reasoning - But the reality is not so easy to describe. (I will try again).
Before I started with OO/LO, I had another workplace with MS-Office as a software package (many years ago). There I used MS Access / MySQL / and other relational databases. The solutions would be long-term.

This workplace mainly uses the LO as office package. (but others like the company have contact with other solutions - usually Excel.)
The data that is processed rarely comes from a database. It is Excel from email or csv files from PDAs..

One difficult thing is managing the data to be processed.
Today there are many different types of files with usually different structures that need to be processed and present different results.
This also applies to files from the same sender/source. Next time, perhaps the result should be presented in a new way.
This results in many solutions with a rather short durability or few occasions/year.
One of the goals is for me to be involved in fewer "conversions"


An example.
There is an Excel file from a supplier.
It may contain macros, message in US currency (decimal point instead of decimal point) spaces before/after a number/text etc.
Before it can be processed, the information must be "cleaned" and adapted to our conditions. i.e. create plain text and numeric fields.

A struktur that is used quite often
a) A spreadsheet in Calc is created.
Data cells from the Excel sheet are copied and only text and numbers are pasted to Tab 1 in Calc. (as a first step).
b) tab 2 is created in Calc - In this tab, formulas are written to convert text to numbers, e.g. 45.3 or 1,000.00 (US notification) to 45.3 or 1.000,00 (Swedish notification)
Handle correctly so e.g. 0003 does not become 3. Clear unnecessary characters. (space before or after text)
Some columns may only contain capital letters (regardless of how the information in Excel looks like.) etc.
How should equations with (eg division by zero) be handled?
How should an empty cell be handled, should it be empty or should it say "0" in the cell. Everything to avoid "errors" and to get as clean a structure as possible on the data.
c) In Tab 3, the structure is adapted to fit a presentation of the result. (Tabs 2 and 3 could be merged, but the formulas can get quite complicated)

This handling works - until row(s) are deleted or added in tab 1
In this case, it would help if the formulas in Tab2 point directly to cells. (instead of the data in the cell)

I don't see how this could be easier to handle in LO Base (maybe someone can show me)
The database / example @UnklDonald418 is a good beginning, but I don't know how to handle the input (right now)

Another example - CSV-files
When it comes to CSV files, @Villeroy is absolutely right. Most files come from Calc or a database.
It is also true that the solutions with Calc are very specific for their task.
Once the information is in a database, many possibilities open up - but before...
But - A common CSV file that we often handle comes from a PDA - in the PDA is data in a SQL? database.
When the PDA connects to the docking station, CSV files are created and saved on the computer (for further management.) and the database is emptied. Depending on what the PDA was used for, the database and result file(s) will have different structures.

Sometimes that information needs to be supplemented from another database. It is possible to read information directly from the database or retrieve information from a CSV file that is exported twice a day.
In these cases, I write a standalone program that handles that information (it's pretty easy since the data is well specified in the CSV file.)
This program can also create a spreeadsheet and write directly to tabs and cells in Calc.
(had wanted to know how I could create a structure/basis in LOBase (without opening Base) - but I haven't found any support for that.

There are requests where a database is the only possible solution.
(but right now I don't know how the input to the database could be automated)

I have another structural question.
Is it possible to create an order document in an editable PDF or Calc (or Base?)?
Don't know if it should be set under Calc, Base, PDF or ... )

[MrProgrammer]

My wish is to compile which suppliers are in both tables ( table1 and table2 ) … which would give the following result:
Supplier_ID1
Supp1
Supp3
Supp4
Supp6
Supp9

Only Supp1, Supp3, and Supp6 are in both of the tables. Based on that list I would say that you want a list of suppliers found in either table.

202302011106.ods

(20.71 KiB) Downloaded 38 times

[Tutorial] The SUMPRODUCT function, example X00

After reviewing my attachment, if you need any additional assistance with this solution attach your spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). Explain why the result in my attachment does not match your expectations. I will not help further unless you attach a spreadsheet to further explain the problem. I ignore all of the pictures that you post, here and in other topics. Pictures do not contain enough information to diagnose the problem and without data I can't test potential solutions, thus it is not worth my time to investigate your question.

I have another structural question. Is it possible to create an order …

You need a new topic for a new question. This one concerns analyzing two tables.


If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Villeroy]

Based on that list I would say that you want a list of suppliers found in either table, not those found in both of them.

In SQL:

Code: Select all

SELECT "Name" FROM "ClientTable_A"
UNION SELECT "Name" FROM "ClientTable_B"

returns unique names occuring in either table.

[Albireo]

.... I would say that you want a list of suppliers found in either table, not those found in both of them.

I would probably describe my desire to present all suppliers once - preferably in ascending order (but no requirement)

Created a spreadsheet to further try to explain my desire

Dual tables v1 - web.ods

(31.2 KiB) Downloaded 40 times

This Spreadsheet has two tabs - Desire - and - Manual handle -

The "Desire" tab contains descriptions of several tabs.
Tab 1 (yellow) and Tab 2 (green) have the source information. and in the "Tab Result" there is a summary.

On the "Tab Result" there are some pink cells, where the summary of the suppliers from TAB 1 and TAB 2 is found
The large field with ??? on a background of Cyan represents some formulas / trix that solve my desire.
__________________________________________________________________

The second Tab - "Manual handle Shows how my wish can be solved, using pivot tables and manual copying. (but my question remains .: Is there a better/easier way?)

[Villeroy]

t109303.ods

(22.12 KiB) Downloaded 41 times

[Albireo]

Yes! (thank you!)
The proposal works, (from some perspective)
But one more column "Tab1" and "Tab2" need to be added.
The base files (CSV files) must also be under the same tab.

(maybe the only way?)
An advantage in analysis is if the tables are side by side.

Dual tables v2 - web.ods

(16.18 KiB) Downloaded 43 times

[Villeroy]

Merge sheets of spreadsheet documents

[Villeroy]

An advantage in analysis is if the tables are side by side.

Which ones?
How many?
This is no problem with a database where data and representation are divided from each other. You can place anything besides anything else.

[MrProgrammer]

Is there a better/easier way?

I would say no. Given the way you've arranged the source data into "dual tables" there is no simple way to get what you'd like. You can only analyze data from dual tables with complex formulas, and perhaps using the Data → Consolidate feature. There are hundreds and hundreds and hundreds of Calc topics from people who split data into pieces and then want to analyze it. You will find that the solution is always: Don't segregate your data!

The problem is twofold:
• You've placed your data source in two separate ranges, so you can't use a pivot table, and have thus lost one of the most important analysis tools in Calc.
• You could join the data into a single range with a new column to differentiate Tab 1 and Tab 2 data, and while you can then get close to your desired layout (see attachment) with a pivot table, they are not infinitely customizable and you will have to accept some limitations in the presentation format.

202302131148.ods

(23.15 KiB) Downloaded 37 times

Calc Guide: Using a Pivot Table   (formerly called Data Pilot)
Calc Guide: Data Analysis   (explains Data → Consolidate)
I have replied twice here, provided two attachments for you to review, and am done contributing to this topic.