Re: [Orgmode] Spreadsheet FR
On Apr 2, 2010, at 10:16 AM, Russell Adams wrote: On Fri, Apr 02, 2010 at 09:49:46AM +0200, Carsten Dominik wrote: Would it be feasible to narrow a table by criteria on a specific field in between separators? Ie: only display those cells in field A if they are 2, or if field B matches Pick Me!. This one might be possible - but dangerous for losing data. Ideally we're just narrowing away lines, collapsing a table like org collapses headlines. Why would that be more likely to cause data loss? Because there would be ellipsis in unfamiliar places and the danger to accidentally modify invisible text would be higer. A nice feature would be updating the totals at the bottom with only the visible data. I don't think Excel works like this, does it? Absolutely. Excel 2003 and up support lists or in 2007 tables, where you can do filter and summary work. I use it all the time for customer planning because its the closest thing to grep/awk for Windows. OK, I did not know this. I think the real trick here is making the formulas ignore narrowed content. If you'd like I can send you a sample. Please do - but that does not mean that I am going to implement this. This would be tricky. - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Spreadsheet FR
On Sun, Apr 04, 2010 at 12:46:28PM -0400, Xiao-Yong Jin wrote: I have an idea. We can leave the original table intact, while making a new dynamically generated table derived from the original one. The org-babel idea was the do similar, but again you will not have the ability to edit the data in the copy. Often times you want to filter and then edit adjacent items... Thanks. -- Russell Adamsrlad...@adamsinfoserv.com PGP Key ID: 0x1160DCB3 http://www.adamsinfoserv.com/ Fingerprint:1723 D8CA 4280 1EC9 557F 66E8 1154 E018 1160 DCB3 ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Spreadsheet FR
On Sun, 4 Apr 2010 15:19:06 -0500, Russell Adams wrote: On Sun, Apr 04, 2010 at 12:46:28PM -0400, Xiao-Yong Jin wrote: I have an idea. We can leave the original table intact, while making a new dynamically generated table derived from the original one. The org-babel idea was the do similar, but again you will not have the ability to edit the data in the copy. Often times you want to filter and then edit adjacent items... I can imagine some kind of backward propagating of data from the derived table to the original one by pressing C-u C-c C-c in the derived table. I was just thinking that it can solve the problem of increasing probability of losing data by miss-typing, because we don't need any extraneous ellipses, and the original table is always there. And there is an advantage of such derived table over an overlay or Excel that is one can make several different tables from a single big one according to different criteria. That might not introduce more difficulties implementing it than a direct overlay, but again, I don't know how to implement it. I guess I am dreaming, now. But I hope such kind of functionality is feasible in practice. -- Jc/*__o/* X\ * (__ Y*/\ ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Spreadsheet FR
On Apr 1, 2010, at 11:46 PM, Russell Adams wrote: Fellow Org'ers, I adore the text spreadsheet, however there's one feature Excel provides which I don't have in org. I often use Excel for lists, where I can sort or narrow the data by specific criteria from a larger list. Would it be feasible to narrow a table by criteria on a specific field in between separators? Ie: only display those cells in field A if they are 2, or if field B matches Pick Me!. This one might be possible - but dangerous for losing data. A nice feature would be updating the totals at the bottom with only the visible data. I don't think Excel works like this, does it? Just like the outline folding the goal would be to hide entries that don't match, they should still remain. So for example: |+| | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | N | 3 | | N | 4 | | Y | 5 | |+| | Total | 17 | |+| #+TBLFM: @8$2=vsum(@-...@-ii) I can already sort by Lime or Cost, but if I filtered where Lime = Y, I would have: |+| | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 |... | Y | 5 | |+| | Total | 10 | |+| #+TBLFM: @8$2=vsum(@-...@-ii) No loss of the lines, on reload or changing the view they would come back, but note that the formula at the end didn't consider them. Ideally the cell references wouldn't change (the example above would break if recalc was hit). I'm not sure whether this would be cumulative or whether the criteria specification would need to allow for multiple logical conditions. Would anyone else consider this a useful feature? I'm not sure how difficult the implementation would be. Thanks. ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode - Carsten ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Spreadsheet FR
On Fri, Apr 02, 2010 at 09:49:46AM +0200, Carsten Dominik wrote: Would it be feasible to narrow a table by criteria on a specific field in between separators? Ie: only display those cells in field A if they are 2, or if field B matches Pick Me!. This one might be possible - but dangerous for losing data. Ideally we're just narrowing away lines, collapsing a table like org collapses headlines. Why would that be more likely to cause data loss? A nice feature would be updating the totals at the bottom with only the visible data. I don't think Excel works like this, does it? Absolutely. Excel 2003 and up support lists or in 2007 tables, where you can do filter and summary work. I use it all the time for customer planning because its the closest thing to grep/awk for Windows. I think the real trick here is making the formulas ignore narrowed content. If you'd like I can send you a sample. Thanks. -- Russell Adamsrlad...@adamsinfoserv.com PGP Key ID: 0x1160DCB3 http://www.adamsinfoserv.com/ Fingerprint:1723 D8CA 4280 1EC9 557F 66E8 1154 E018 1160 DCB3 ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Spreadsheet FR
How can you expand/narrow the disabled lines if you want to show/hide them? This will probably interfere with normal TAB use inside a table. I think that the ability to disable some lines based on data in a column, such as in your example, can be very useful. But maybe we don't need to really collapse the lines. Only changing the face could be enough. - Darlan At Fri, 2 Apr 2010 03:16:58 -0500, Russell Adams rlad...@adamsinfoserv.com wrote: On Fri, Apr 02, 2010 at 09:49:46AM +0200, Carsten Dominik wrote: Would it be feasible to narrow a table by criteria on a specific field in between separators? Ie: only display those cells in field A if they are 2, or if field B matches Pick Me!. This one might be possible - but dangerous for losing data. Ideally we're just narrowing away lines, collapsing a table like org collapses headlines. Why would that be more likely to cause data loss? A nice feature would be updating the totals at the bottom with only the visible data. I don't think Excel works like this, does it? Absolutely. Excel 2003 and up support lists or in 2007 tables, where you can do filter and summary work. I use it all the time for customer planning because its the closest thing to grep/awk for Windows. I think the real trick here is making the formulas ignore narrowed content. If you'd like I can send you a sample. Thanks. -- Russell Adamsrlad...@adamsinfoserv.com PGP Key ID: 0x1160DCB3 http://www.adamsinfoserv.com/ Fingerprint:1723 D8CA 4280 1EC9 557F 66E8 1154 E018 1160 DCB3 ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Spreadsheet FR
Hi Russell, Carsten Dominik carsten.domi...@gmail.com writes: On Apr 1, 2010, at 11:46 PM, Russell Adams wrote: Fellow Org'ers, I adore the text spreadsheet, however there's one feature Excel provides which I don't have in org. I often use Excel for lists, where I can sort or narrow the data by specific criteria from a larger list. Would it be feasible to narrow a table by criteria on a specific field in between separators? Ie: only display those cells in field A if they are 2, or if field B matches Pick Me!. How about keeping a master table containing all the information, and then generating narrowed views as separate tables? The babel way to do this would be to have a block function filter-table (provided below) and then call it where needed: #+TBLNAME: lime-table | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | N | 3 | | N | 4 | | Y | 5 | | Total | 17 | #+TBLFM: @8$2=vsum(@-...@-ii) #+call: filter-table(table=lime-table, field=0, value=Y) #+results: filter-table(table=lime-table, field=0, value=Y) | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | Y | 5 | As you can see I got rid of a few horizontal separator lines, and we don't currently have totals. I'll suggest fixes for that below, but my main point is that although it may make sense to extend org-mode, it's already easy to do this in org-babel. The separator lines are details, we can fix that by tinkering (if there's interest we could continue this thread to do so). As for the totals, we could go two routes: 1. We could add a final total row and use a table formula to compute the total 2. We could add the total row and compute the totals in a block function. As far as I know there's no way to add a final row using a table formula -- is that right? But we could automate (1) by wrapping the call to filter-table in a second block function that is specific to this problem: #+call: filter-with-total-line(table=lime-table, field=0, value=Y) #+results: filter-with-total-line(table=lime-table, field=0, value=Y) | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | Y | 5 | | Total | 10 | #+TBLFM: @6$2=vsum(@-I..) OK, so I hard wired a 6 into the TBLFM line. Perhaps someone can tell me how to refer to the last line of the table. Also, it might be nice if babel could apply the table formula automaticaly after generating the table. Here are the block functions. Once someone has written them, nobody else needs to know anything about the implementation. They can be placed in the same file (or in a different file, and added to your library of babel using org-babel-lob-ingest). I've already added filter-table to LoB on Worg. #+function: filter-table(table, field, value) #+begin_src emacs-lisp (defun org-lob-filter-table (table field value) (if (and ( (length table) 1) (eq (second table) 'hline)) (append (list (first table) 'hline) (org-lob-filter-table (cddr table) field value)) (delq nil (mapcar (lambda (row) (cond ((eq row 'hline) 'hline) ((equal (nth field row) value) row))) table (org-lob-filter-table table field value) #+end_src #+function: filter-with-total-line(table, field, value) #+begin_src emacs-lisp (append (org-lob-filter-table table field value) '((Total ))) #+end_src For those who are still reading, 1. These are written in elisp so that they're usable by anyone, although they would be one-liners in R. 2. The second one uses a function defined in the first one. This may be OK, but a perhaps preferable solution would be #+function: append-total-line(table) #+begin_src emacs-lisp (append table '((Total ))) #+end_src #+call: append-total-line(table=filter-table(table=lime-table, field=0, value=Y)) Dan This one might be possible - but dangerous for losing data. A nice feature would be updating the totals at the bottom with only the visible data. I don't think Excel works like this, does it? Just like the outline folding the goal would be to hide entries that don't match, they should still remain. So for example: |+| | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | N | 3 | | N | 4 | | Y | 5 | |+| | Total | 17 | |+| #+TBLFM: @8$2=vsum(@-...@-ii) I can already sort by Lime or Cost, but if I filtered where Lime = Y, I would have: |+| | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 |... | Y | 5 | |+| | Total | 10 | |+|
Re: [Orgmode] Spreadsheet FR
On Fri, Apr 02, 2010 at 10:44:23AM -0400, Dan Davison wrote: How about keeping a master table containing all the information, and then generating narrowed views as separate tables? The babel way to do this would be to have a block function filter-table (provided below) and then call it where needed: #+TBLNAME: lime-table | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | N | 3 | | N | 4 | | Y | 5 | | Total | 17 | #+TBLFM: @8$2=vsum(@-...@-ii) #+call: filter-table(table=lime-table, field=0, value=Y) #+results: filter-table(table=lime-table, field=0, value=Y) | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | Y | 5 | As you can see I got rid of a few horizontal separator lines, and we don't currently have totals. I'll suggest fixes for that below, but my main point is that although it may make sense to extend org-mode, it's already easy to do this in org-babel. That's a better way to do it than properties in my opinion. Whether extension was required or not, my only point was that for small lists the only reason I still use OO or Excel was for the ability to dynamically narrow a list. Given org narrows outlines, I thought it might fit. I'll have to play with babel now! OK, so I hard wired a 6 into the TBLFM line. Perhaps someone can tell me how to refer to the last line of the table. Also, it might be nice if babel could apply the table formula automaticaly after generating the table. This is why I use @-...@-ii, the separators are your cues. There's an LR symbol, but I don't use it often. That alone is a good reason to make sure separator lines are preserved. Thanks. -- Russell Adamsrlad...@adamsinfoserv.com PGP Key ID: 0x1160DCB3 http://www.adamsinfoserv.com/ Fingerprint:1723 D8CA 4280 1EC9 557F 66E8 1154 E018 1160 DCB3 ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Spreadsheet FR
On Fri, Apr 02, 2010 at 09:55:16AM -0500, Russell Adams wrote: As you can see I got rid of a few horizontal separator lines, and we don't currently have totals. I'll suggest fixes for that below, but my main point is that although it may make sense to extend org-mode, it's already easy to do this in org-babel. That's a better way to do it than properties in my opinion. Whether extension was required or not, my only point was that for small lists the only reason I still use OO or Excel was for the ability to dynamically narrow a list. Given org narrows outlines, I thought it might fit. One issue I just realized is that babel in this format will strictly be a *viewer*, that you could not reduce the dataset and edit it in adjacent lines. -- Russell Adamsrlad...@adamsinfoserv.com PGP Key ID: 0x1160DCB3 http://www.adamsinfoserv.com/ Fingerprint:1723 D8CA 4280 1EC9 557F 66E8 1154 E018 1160 DCB3 ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
[Orgmode] Spreadsheet FR
Fellow Org'ers, I adore the text spreadsheet, however there's one feature Excel provides which I don't have in org. I often use Excel for lists, where I can sort or narrow the data by specific criteria from a larger list. Would it be feasible to narrow a table by criteria on a specific field in between separators? Ie: only display those cells in field A if they are 2, or if field B matches Pick Me!. A nice feature would be updating the totals at the bottom with only the visible data. Just like the outline folding the goal would be to hide entries that don't match, they should still remain. So for example: |+| | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 | | N | 3 | | N | 4 | | Y | 5 | |+| | Total | 17 | |+| #+TBLFM: @8$2=vsum(@-...@-ii) I can already sort by Lime or Cost, but if I filtered where Lime = Y, I would have: |+| | *Lime* | *Cost* | |+| | Y | 1 | | Y | 2 | | Y | 2 |... | Y | 5 | |+| | Total | 10 | |+| #+TBLFM: @8$2=vsum(@-...@-ii) No loss of the lines, on reload or changing the view they would come back, but note that the formula at the end didn't consider them. Ideally the cell references wouldn't change (the example above would break if recalc was hit). I'm not sure whether this would be cumulative or whether the criteria specification would need to allow for multiple logical conditions. Would anyone else consider this a useful feature? I'm not sure how difficult the implementation would be. Thanks. ___ Emacs-orgmode mailing list Please use `Reply All' to send replies to the list. Emacs-orgmode@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
