Register Guidelines E-Books Search Today's Posts Mark Forums Read

Go Back   MobileRead Forums > E-Book Software > Calibre > Library Management

Notices

Reply
 
Thread Tools Search this Thread
Old 06-24-2019, 03:50 PM   #1
Soap-dodger
Member
Soap-dodger began at the beginning.
 
Soap-dodger's Avatar
 
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
Post Arithmetic in Custom Column


Is it possible to have a formula in a custom column that performs an arithmetic operation on the values of two other columns? For example, I have a custom column (#words) that contains the number of words in each book, and another custom column (#pages) that contains the number of pages in each book. I would like to have another custom column that shows the average number of words per page, so it would divide #words by #pages, provided both #words and #pages contain positive integer values. How might I achieve this?
Soap-dodger is offline   Reply With Quote
Old 06-24-2019, 05:27 PM   #2
BetterRed
null operator
BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.
 
Posts: 15,721
Karma: 18415750
Join Date: Mar 2012
Location: Sydney Australia
Device: none
See Arithmetic functions in Template language - Function classification

There's sticky at the top of this sub-forum that might have some examples of usage. And searches for 'arithmetic', 'math' and 'calculate' should find some more.

If your #words and #pages columns originate from the Count Pages plugin you could create a private version of the PI and do the calculate in there - in python. Plugin zips contain the source code.

BR
BetterRed is offline   Reply With Quote
Advert
Old 06-25-2019, 06:12 AM   #3
Soap-dodger
Member
Soap-dodger began at the beginning.
 
Soap-dodger's Avatar
 
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
Quote:
Originally Posted by BetterRed View Post
See Arithmetic functions in Template language - Function classification

BR
Thanks for the link. I did have a look there and it did help somewhat.

I have almost got this working now, though I still have a couple of issues to iron out, and any help you might offer will be appreciated.

First, in order to ensure that I do not attempt the division if either #words or #pages have null or zero values, I first created a temporary custom column called #ready, which has the following template:

Code:
{:'and(field('#words'), strcmp(field('#words'), "", "", "", cmp(field('#words'), 0, "", "", field('#words'))), field('#pages'), strcmp(field('#pages'), "", "", "", cmp(field('#pages'), 0, "", "", field('#pages'))))'}
This seems to work perfectly, returning a "1" only when the division makes sense, else it is empty.

Using #ready as a base, I then created another custom field to carry out the division and called it #awp (average words per page). This field has the template:

Code:
{:'test(and(field('#words'), strcmp(field('#words'), "", "", "", cmp(field('#words'), 0, "", "", field('#words'))), field('#pages'), strcmp(field('#pages'), "", "", "", cmp(field('#pages'), 0, "", "", field('#pages')))), divide(field('#words'), field('#pages')), "")'}
Apart from one use case, this works, returning a floating point value. I need to work out how to round the returned value to a whole number, though.

The only time it doesn't work is when both #words and #pages contain null values. In this case, I get the following error message:

Code:
EXCEPTION:  float division by zero
Can you help solve these issues?
Soap-dodger is offline   Reply With Quote
Old 06-25-2019, 06:19 AM   #4
Soap-dodger
Member
Soap-dodger began at the beginning.
 
Soap-dodger's Avatar
 
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
Quote:
Originally Posted by BetterRed View Post

If your #words and #pages columns originate from the Count Pages plugin you could create a private version of the PI and do the calculate in there - in python. Plugin zips contain the source code.

BR
Yes, both of my #words and #pages columns originate from the Count Pages plugin. I appreciate your suggestion regarding creating a private version of the plugin, however, my proficiency with Python is not yet up to the task.
Soap-dodger is offline   Reply With Quote
Old 06-25-2019, 06:37 AM   #5
BetterRed
null operator
BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.BetterRed ought to be getting tired of karma fortunes by now.
 
Posts: 15,721
Karma: 18415750
Join Date: Mar 2012
Location: Sydney Australia
Device: none
@Soap-dodger - I always have to hack templates by trial and error, so I won't try to answer your questions but I would have tried to use a Program Mode template. BTW the ifempty function will test for Undefined (null).

Hopefully one of the template experts, such as chaley, davidfor will chime in.

BR
BetterRed is offline   Reply With Quote
Advert
Old 06-25-2019, 07:53 AM   #6
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 18,590
Karma: 30739326
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
Quote:
Originally Posted by BetterRed View Post
Hopefully one of the template experts, such as chaley, davidfor will chime in.
And personally, I hope chaley comes along first

But...

If it isn't obvious, I usually do it in program mode first. This is what I got:

Code:
program:
    pages = cmp(raw_field('#pages'),0,1,1,raw_field('#pages'));
    words = cmp(raw_field('#pages'),0,0,0,raw_field('#words'));
    divide(words,pages);
The idea is to get a non-zero number for the page count no matter what, and use zero for th word count if the page count was supposed to be zero. That gives zero for the words per page if there are no pages. But, maybe it should be the word count or something else.

In non-program mode this becomes:
Code:
{:'divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages")))'}
And for the record, the first attempt was:
Code:
{#pages:'test($,divide(raw_field("#words"),raw_field("#pages")),0)'}
But, the "divide" is always executed even if the the first value is empty. I wasn't expecting that.

To get an integer, you can use "format_number" with an appropriate format template.
davidfor is offline   Reply With Quote
Old 06-25-2019, 11:05 AM   #7
thiago.eec
Addict
thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.thiago.eec knows who John Galt is.
 
Posts: 300
Karma: 71602
Join Date: Dec 2016
Location: Goiânia - Brazil
Device: iPad, Kindle Paperwhite
Quote:
Originally Posted by Soap-dodger View Post
Apart from one use case, this works, returning a floating point value. I need to work out how to round the returned value to a whole number, though.

The only time it doesn't work is when both #words and #pages contain null values. In this case, I get the following error message:

Code:
EXCEPTION:  float division by zero
Can you help solve these issues?
Starting with the code posted by @davidfor, you could use something like the code below to format numbers as integers and use 'undefined' instead as '0', since those will appear on the book details pannel (that bothers me ).

Code:
program:
    pages = cmp(raw_field('#pages'),0,1,1,raw_field('#pages'));
    words = cmp(raw_field('#pages'),0,0,0,raw_field('#words'));
    wpp = format_number(divide(words,pages),'{0:.0f}');
    cmp(wpp, 0, '', '', wpp)

Last edited by thiago.eec; 06-25-2019 at 03:07 PM.
thiago.eec is offline   Reply With Quote
Old 06-26-2019, 03:03 AM   #8
Soap-dodger
Member
Soap-dodger began at the beginning.
 
Soap-dodger's Avatar
 
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
Quote:
Originally Posted by BetterRed View Post
@Soap-dodger
BTW the ifempty function will test for Undefined (null).
... for the hint.
Soap-dodger is offline   Reply With Quote
Old 06-26-2019, 03:53 AM   #9
Soap-dodger
Member
Soap-dodger began at the beginning.
 
Soap-dodger's Avatar
 
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
Quote:
Originally Posted by davidfor View Post
To get an integer, you can use "format_number" with an appropriate format template.
You are so kind; your solution works excellently.

I am having difficulty in converting the result to an integer. I have read the relevant section of the Calibre manual to no avail. Here's what I've tried so far:

Code:
format_number(divide(words,pages), {0:3.0f});
format_number(divide(words,pages), "{0:3.0f}");
format_number(divide(words,pages), {0:,d});
format_number(divide(words,pages), "{0:,d}");
Either the manual could be clearer or I'm missing something.

Please, can you advise where I've got it wrong?
Soap-dodger is offline   Reply With Quote
Old 06-26-2019, 08:01 AM   #10
davidfor
Grand Sorcerer
davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.davidfor ought to be getting tired of karma fortunes by now.
 
Posts: 18,590
Karma: 30739326
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
Quote:
Originally Posted by Soap-dodger View Post
You are so kind; your solution works excellently.

I am having difficulty in converting the result to an integer. I have read the relevant section of the Calibre manual to no avail. Here's what I've tried so far:

Code:
format_number(divide(words,pages), {0:3.0f});
format_number(divide(words,pages), "{0:3.0f}");
format_number(divide(words,pages), {0:,d});
format_number(divide(words,pages), "{0:,d}");
Either the manual could be clearer or I'm missing something.

Please, can you advise where I've got it wrong?
The one that worked for me was:

Code:
format_number(divide(words,pages), "{0:3.0f}");
But, if you do it in template mode, you need to remove the braces. So, it would be:
Code:
{:'format_number(divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages"))), "3.0f")'}
davidfor is offline   Reply With Quote
Old 06-26-2019, 09:00 AM   #11
Soap-dodger
Member
Soap-dodger began at the beginning.
 
Soap-dodger's Avatar
 
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
Quote:
Originally Posted by davidfor View Post
The one that worked for me was:

Code:
format_number(divide(words,pages), "{0:3.0f}");
But, if you do it in template mode, you need to remove the braces. So, it would be:
Code:
{:'format_number(divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages"))), "3.0f")'}
Got it working now with your help.
Soap-dodger is offline   Reply With Quote
Reply

Tags
arithmetic, custom column

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding custom column with any lookup-name gives me empty column as a result? Ahu Lee Library Management 16 06-09-2019 12:14 PM
Move selected data from series column to a new custom column fvdham Library Management 3 06-02-2017 03:49 PM
Custom column returns value based on value of another custom column? calvin-c Calibre 3 09-14-2013 02:24 PM
Custom yes/no column built from long text column Philantrop Library Management 7 03-23-2013 07:44 PM
how to move value(s) of tag column to a custom made column zoorakhan Library Management 0 12-08-2012 03:53 AM


All times are GMT -4. The time now is 05:18 AM.


MobileRead.com is a privately owned, operated and funded community.