Help with searching two columns

[SnakeCharmer]

Hi all,

I need some help with Calibre. I'm trying to search through two different columns for similarities (on the entire library, not just a single book). Is it possible?

I'll explain a bit more:

I started my library with the basic columns, and then a while ago I added a column (#secondseries) for second series information.

The problem is, as I added more books afterwards, I started using the series column a bit differently and never got around to fixing the older books (because there's too many of them, and I don't always know the series name by heart, so I can't really compare manually).

So I ended up with stuff like: "Book 1" with Series "12 Days.Advent Calendar [1]" and #secondseries empty, while the new books are "Book 2" with Series "12 Days [14]" and #secondseries "Advent Calendar [2]" (which is more accurate as they're yearly series).

So basically what I want to do is have a sort of "find duplicates" but where I can choose any two columns (series, in this case, but I suppose cross-referencing keywords through any two columns might be useful) and find matching keywords or similarities (even if I don't know the keyword).

Is there a way to do this? Either via search or a plugin I missed or something?

(I hope that made sense, I usually feel like I make a mess when I'm trying to explain stuff)

[Sabardeyn]

I cannot think of a particularly good way to handle a situation like this.

You might be able to perform a search with series:true AND secondseries:false. This would show you any books which have info in the series column, but nothing in the secondseries column. You would still have to manually slog through all the results, updating entries as you go. You can then sort on series or secondseries depending on whichever similar wording you're looking for at the time. I would think you might need to sort on both, just to make sure you don't miss anything.

You will still need a way to find and compare books where there is info in both columns though. Obviously setting both to true will get a listing and sorting will help to group similar series names. It won't be perfect and you'll still have to do a lot of manual editing.

[SnakeCharmer]

Thank you for the reply Sabardeyn! Hmm, that might be helpful. It won't be perfect, as you say, but it'll probably help me thin down the list considerably. Thanks!

If anyone has any other ideas, they'll be quite welcome.

[Sabardeyn]

You're very welcome.

Conditional searches like I suggested above means calibre will remove a book from display when you enter anything into the secondseries column. After all, once there is something in secondseries that title no longer meets your search criteria.

So if you see something else that needs editing, do that first. Then correct for the missing secondseries info. Otherwise you'll lose track of it.


Only other things I would suggest is to run the Find Duplicates and Quality Check plugins. They're invaluable for spotting minor errors. Depending on just how much, and how accurate, you want your metadata to be.

[BetterRed]

I wonder if this could be done with a third column (true false), which is based on the results of a template that compares the two columns in some way. See http://manual.calibre-ebook.com/template_lang.html

My skills in this facet of calibre are scratchy at best but I think that's where I'd start experimenting. Templates have several modes of operation - simple, advanced, single function, template program and general program. I suspect you'd need to use one of the program modes.

BR

[SnakeCharmer]

Originally Posted by Sabardeyn

Only other things I would suggest is to run the Find Duplicates and Quality Check plugins. They're invaluable for spotting minor errors. Depending on just how much, and how accurate, you want your metadata to be.

Oh yes, I run Find Duplicates often. Quality Check I've only just starting to mess with more properly, but I'm trying to form a habit of running one when I run the other, which is usually after adding a considerable batch of books.

Originally Posted by BetterRed

I wonder if this could be done with a third column (true false), which is based on the results of a template that compares the two columns in some way. See http://manual.calibre-ebook.com/template_lang.html

My skills in this facet of calibre are scratchy at best but I think that's where I'd start experimenting. Templates have several modes of operation - simple, advanced, single function, template program and general program. I suspect you'd need to use one of the program modes.

BR

That's quite interesting. I'm going to have to start messing with the templates. I only ever did so for the save to disk feature (I don't think my brain even registered I could use them in columns too before now, lol), so I think it's safe to say my own skills are it are non-existent.
Thank you!!

[BetterRed]

Originally Posted by SnakeCharmer

I'm going to have to start messing with the templates. I only ever did so for the save to disk feature (I don't think my brain even registered I could use them in columns too before now, lol), so I think it's safe to say my own skills are it are non-existent.
Thank you!!

Give chaley a nudge - I think templates are his baby, and he's alwsys willing to help.

BR

[chaley]

Originally Posted by SnakeCharmer

That's quite interesting. I'm going to have to start messing with the templates. I only ever did so for the save to disk feature (I don't think my brain even registered I could use them in columns too before now, lol), so I think it's safe to say my own skills are it are non-existent.
Thank you!!

Originally Posted by BetterRed

Give chaley a nudge - I think templates are his baby, and he's alwsys willing to help.

BR

It would be easy to create a column template that checked if 2 (or N) other columns contain any words in common. Use the list_intersection function if either column is multi-value or the strcmp function if both columns are single-value. However, I suspect that satisfying the real requirement would require some level of fuzziness, and this would be much harder.

[BetterRed]

Originally Posted by chaley

... I suspect that satisfying the real requirement would require some level of fuzziness, and this would be much harder.

Which is what I had in mind when I wrote "...compares the two columns in some way"

May the OP could borrow from what kiwidude has in his Fine Duplicates PI.

BR

[SnakeCharmer]

Thanks again BetterRed!

Originally Posted by chaley

It would be easy to create a column template that checked if 2 (or N) other columns contain any words in common. Use the list_intersection function if either column is multi-value or the strcmp function if both columns are single-value. However, I suspect that satisfying the real requirement would require some level of fuzziness, and this would be much harder.

Thank you!!
Is the series column considered multi-value? Since it also has the series number along with the name.

[chaley]

Originally Posted by SnakeCharmer

Is the series column considered multi-value? Since it also has the series number along with the name.

No, the series column is single value. In templates, the index is separate from the series name, i.e., {series} and {series_index}.

To check if series and #secondseries are the same, use something like the following in a column built from other columns

{series:'strcmp($, field('#secondseries'), '', $, '')'}

This template will output the series name if the two columns contain the same value, otherwise it outputs nothing.

You could do a bit of fuzzier searching by using a "contains" match instead of an exact match. Something like:

program:
	a = field('series');
	b = field('#secondseries');
	first_non_empty(
		test(b, contains(a, b, b, ''), ''),
		test(a, contains(b, a, a, ''), '')
	)

[SnakeCharmer]

Oh! You're right, I forgot series_index is separate.

I'll give a try to the first code tonight and see what turns up, and I'll give a try to the program this weekend, since I have no clue how to properly use it and thus will require more investigating.

Thank you so much for the help!

[chaley]

Originally Posted by SnakeCharmer

I'll give a try to the first code tonight and see what turns up, and I'll give a try to the program this weekend, since I have no clue how to properly use it and thus will require more investigating.

The program: stuff (a template in general program mode) goes in the same place as the other template. It works by checking of the content of a column is not empty, then is that content contained within the other column. It has to do this for each column, because (a contains b) does not imply (b contains a) unless (a equals b).

Hint: you can edit the template in a column built from other columns from the spreadsheet without restarting calibre. Simply click on any cell in the column and press F2, or whatever the "edit" key is on your system.

Thank you so much for the help!

You are welcome. I am glad to see people interested in learning what the template language can do and how to use it.