remote calls to tables and (empty entry, NAN)

remote calls to tables and (empty entry, NAN)

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 1655 bytes --]



Hi

The following works nicely 

#+begin_src 
#+NAME: table1
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith  |   2 |   3 |   4 |   6 |   7 |     22 |
| Miller |   2 |  10 |   1 |   1 |   5 |     19 |
| Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
#+TBLFM: $7=vsum($2..$6);f2


#+Name: final
| Name   | ResSh1 | ResSh2 | Total |
|--------+--------+--------+-------|
| Smith  |     22 |        |       |
| Miller |     19 |        |       |
| Wick   |     18 |        |       |
#+TBLFM: $2=remote(table1,@@#$7)
#+end_src


However if in table1 there is an empty entry in the relevant column,
remote copies it as 0

#+begin_src 

#+NAME: table2
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith  |     |   3 |   4 |   6 |   7 |        |
| Miller |   2 |  10 |   1 |   1 |   5 |     19 |
| Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
#+TBLFM: $7=if("$2" == "nan", string(""),vsum($2..$6)); E f-2


#+Name: total
| Name   | ResSh1 | ResSh2 | Total |
|--------+--------+--------+-------|
| Smith  |      0 |        |       |
| Miller |     19 |        |       |
| Wick   |     18 |        |       |
#+TBLFM: $2=remote(table2,@@#$7)
#+end_src

Any idea how to obtain an empty entry instead of 0?

Thanks and regards

Uwe Brauer 

-- 
I strongly condemn Hamas heinous despicable pogroms/atrocities on Israel
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military. 
I support the EU and NATO membership of Ukraine. 


[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: remote calls to tables and (empty entry, NAN)

[Bruno Barbier]

Hi,

Uwe Brauer <oub@mat.ucm.es> writes:

> However if in table1 there is an empty entry in the relevant column,
> remote copies it as 0
>
> #+begin_src 
>
> #+NAME: table2
> | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Smith  |     |   3 |   4 |   6 |   7 |        |
> | Miller |   2 |  10 |   1 |   1 |   5 |     19 |
> | Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
> #+TBLFM: $7=if("$2" == "nan", string(""),vsum($2..$6)); E f-2
>
>
> #+Name: total
> | Name   | ResSh1 | ResSh2 | Total |
> |--------+--------+--------+-------|
> | Smith  |      0 |        |       |
> | Miller |     19 |        |       |
> | Wick   |     18 |        |       |
> #+TBLFM: $2=remote(table2,@@#$7)
> #+end_src
>
> Any idea how to obtain an empty entry instead of 0?

This works for me:

   #+TBLFM: $2=if(typeof(remote(table2, @@#$7)) == 12, string(""), remote(table2, @@#$7)); E

(everything is documented here:
   https://orgmode.org/manual/Formula-syntax-for-Calc.html#Formula-syntax-for-Calc
)

This works too, although I'm not sure it should:

   #+TBLFM: $2=if("remote(table2, @@#$7)" = "nan", string(""), remote(table2, @@#$7)); E

HTH,

Bruno

Re: remote calls to tables and (empty entry, NAN)

[Uwe Brauer via General discussions about Org-mode.]

[-- Attachment #1: Type: text/plain, Size: 684 bytes --]


> Hi,

> Uwe Brauer <oub@mat.ucm.es> writes:


> This works for me:

>    #+TBLFM: $2=if(typeof(remote(table2, @@#$7)) == 12, string(""), remote(table2, @@#$7)); E

Thanks very much!

> (everything is documented here:
>    https://orgmode.org/manual/Formula-syntax-for-Calc.html#Formula-syntax-for-Calc
> )

> This works too, although I'm not sure it should:

>    #+TBLFM: $2=if("remote(table2, @@#$7)" = "nan", string(""), remote(table2, @@#$7)); E

This I tried but without the " in front of remote

$2=if(remote(table2, @@#$7)" = "nan", string(""), remote(table2, @@#$7)); E


And obtained an error. I think I will stick to the typeof syntaxes.

Uwe 

[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

Re: remote calls to tables and (empty entry, NAN)

[Uwe Brauer]

[-- Attachment #1: Type: text/plain, Size: 2789 bytes --]


Hi again

> Uwe Brauer <oub@mat.ucm.es> writes:


> This works for me:

>    #+TBLFM: $2=if(typeof(remote(table2, @@#$7)) == 12, string(""), remote(table2, @@#$7)); E



I just realized that the @@#$7 is not save against errors
#+begin_src 

* Simple remote
#+Name: table1
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith  |   2 |   3 |   4 |   6 |   7 |        |
| Miller |   2 |  10 |   1 |   1 |   5 |     19 |
| Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
#+TBLFM: $7=vsum($2..$6);f2


#+Name: table2   
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|--------+-----+-----+-----+-----+-----+--------|
| Miller |   9 |   4 |   6 |   9 |   3 |     31 |
| Smith   |   8 |   3 |   5 |   8 |   9 |     33 |
| Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
|--------+-----+-----+-----+-----+-----+--------|
#+TBLFM: $7=vsum($2..$6);f2

#+Name: final
| Name   | ResSh1 | ResSh2 |
|--------+--------+--------|
| Smith  |        |     31 |
| Miller |     19 |     33 |
| Wick   |     18 |     18 |
|--------+--------+--------|
#+TBLFM: $2=if(typeof(remote(table1, @@#$7)) == 12, string(""), remote(table1, @@#$7)); E::$3=remote(table2,@@#$7)



#+end_src

As you can see in the table2 the row Miller-and-Smith have been
interchanged and so the final table ends up with incorrect values
However, (as somebody else suggested some time ago) the following
solution avoids that problem.

#+begin_src 


#+NAME: table3
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
|--------+-----+-----+-----+-----+-----+--------|
| Smith  |   2 |   3 |   4 |   6 |   7 |     22 |
| Miller |   2 |  10 |   1 |   1 |   5 |     19 |
| Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
#+TBLFM: $7=vsum($2..$6);f2

#+NAME: table4   
| Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
|--------+-----+-----+-----+-----+-----+--------|
| Miller |   2 |   1 |   6 |   9 |   3 |     21 |
| Smith  |   8 |   3 |   5 |   8 |   9 |     33 |
| Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
#+TBLFM: $7=vsum($2..$6);f2

#+Name: final
| Name   | ResSh1 | ResSh2 | Total |
|--------+--------+--------+-------|
| Smith  |     22 | 33     |       |
| Miller |     19 | 21     |       |
| Wick   |     18 | 18     |       |
#+TBLFM: $2='(org-lookup-first $1 '(remote(table3, @1$1..@4$1)) '(remote(table3, @1$7..@4$7)))::$3='(org-lookup-first $1 '(remote(table4, @1$1..@4$1)) '(remote(table4, @1$7..@4$7)))

#+end_src
But I don't know you use use typeof in that case

Any ideas?

#+TBLFM: $2='(org-lookup-first $1 '(remote(table3, @1$1..@4$1)) if(typeof('(remote(table3, @1$7..@4$7))) == 12, string(""),(remote(table3, @1$7..@4$7))); E

Does not work

Uwe Brauer 






[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]

[[SOLVED?]] (was: remote calls to tables and (empty entry, NAN))

[Uwe Brauer via General discussions about Org-mode.]

[-- Attachment #1: Type: text/plain, Size: 1980 bytes --]

>>> "UB" == Uwe Brauer <oub@mat.ucm.es> writes:

> Hi again

>> Uwe Brauer <oub@mat.ucm.es> writes:


>> This works for me:

>> #+TBLFM: $2=if(typeof(remote(table2, @@#$7)) == 12, string(""), remote(table2, @@#$7)); E

> As you can see in the table2 the row Miller-and-Smith have been
> interchanged and so the final table ends up with incorrect values
> However, (as somebody else suggested some time ago) the following
> solution avoids that problem.

> #+begin_src 


> #+NAME: table3
> | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh1 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Smith  |   2 |   3 |   4 |   6 |   7 |     22 |
> | Miller |   2 |  10 |   1 |   1 |   5 |     19 |
> | Wick   |   1 |   2 |   3 |  10 |   2 |     18 |
> #+TBLFM: $7=vsum($2..$6);f2

> #+NAME: table4   
> | Name   | Ex1 | Ex2 | Ex2 | Ex4 | Ex5 | ResSh2 |
> |--------+-----+-----+-----+-----+-----+--------|
> | Miller |   2 |   1 |   6 |   9 |   3 |     21 |
> | Smith  |   8 |   3 |   5 |   8 |   9 |     33 |
> | Wick   |   1 |   5 |   9 |   1 |   2 |     18 |
> #+TBLFM: $7=vsum($2..$6);f2

> #+Name: final
> | Name   | ResSh1 | ResSh2 | Total |
> |--------+--------+--------+-------|
> | Smith  |     22 | 33     |       |
> | Miller |     19 | 21     |       |
> | Wick   |     18 | 18     |       |
> #+TBLFM: $2='(org-lookup-first $1 '(remote(table3, @1$1..@4$1)) '(remote(table3, @1$7..@4$7)))::$3='(org-lookup-first $1 '(remote(table4, @1$1..@4$1)) '(remote(table4, @1$7..@4$7)))

> #+end_src


It seems that just adding ; E at the end of the line solves the problem


#+TBLFM: $2='(org-lookup-first $1 '(remote(sheet1, @I$1..@II$1)) '(remote(sheet1, @I$2..@II$2))); E 



-- 
I strongly condemn Hamas heinous despicable pogroms/atrocities on Israel
I strongly condemn Putin's war of aggression against Ukraine.
I support to deliver weapons to Ukraine's military. 
I support the EU and NATO membership of Ukraine. 


[-- Attachment #2: smime.p7s --]
[-- Type: application/pkcs7-signature, Size: 5673 bytes --]