| T O P I C R E V I E W |
| AKazak |
Posted - 06/30/2022 : 05:36:58 AM
OriginPro 2022b (64-bit) SR1 9.9.5.167
Windows 7 Pro SP1 x64
Greetings!
I want to create a user parameter with a formula that takes Long Name from the same column and returns results of vertical lookup from a different sheet?
Can you share a good example of this, please?
---
Andrey |
| 29 L A T E S T R E P L I E S (Newest First) |
| lagaaduu |
Posted - 11/23/2022 : 05:30:17 AM
Thank you for your clarification. Yes, I can reproduce the issue. It doesn't work by setting formula.
|
| AKazak |
Posted - 11/02/2022 : 03:50:16 AM
quote:
Originally posted by minimax
%(str$) requires a variable, but yours is a function return value.
I guess it does not work.
Got it!
---
Andrey |
| minimax |
Posted - 11/02/2022 : 03:03:09 AM
%(str$) requires a variable, but yours is a function return value.
I guess it does not work. |
| AKazak |
Posted - 11/02/2022 : 02:14:04 AM
quote:
Originally posted by minimax
quote:
Is this allowed to use Substitution Notation in column formulae
It should allow.
In my system the column formula:
=%(lookup(This[SID]$,"Data"!B,"Data"!X)$)
doesn't work.
---
Andrey |
| minimax |
Posted - 11/02/2022 : 01:08:18 AM
quote:
Is below the correct syntax for lookup to return numeric values instead of strings?
Yes.
quote:
Is this allowed to use Substitution Notation in column formulae
It should allow. |
| AKazak |
Posted - 11/01/2022 : 01:59:39 AM
quote:
Originally posted by minimax
You may take a look at these pages:
https://www.originlab.com/doc/en/Origin-Help/Using-Formulae-to-Set-Cell-Values#Referring_to_Worksheet_Columns_using_.22This.22
https://www.originlab.com/doc/en/Origin-Help/Wks-SetColVal-MenuOpt-Dialog
This[SID]$ refers to current column's label row "SID"'s cell value (string type).
Thank you for the suggestion.
The formula:
=lookup(This[SID]$,"Data"!B,"Data"!X)$
works fine, that is delivers expected results.
Is below the correct syntax for lookup to return numeric values instead of strings?
=lookup(This[SID]$,"Data"!B,"Data"!X)
Is this allowed to use Substitution Notation as per https://www.originlab.com/doc/LabTalk/guide/String-Conversion in column formulae?
---
Andrey |
| minimax |
Posted - 11/01/2022 : 01:13:54 AM
You may take a look at these pages:
https://www.originlab.com/doc/en/Origin-Help/Using-Formulae-to-Set-Cell-Values#Referring_to_Worksheet_Columns_using_.22This.22
https://www.originlab.com/doc/en/Origin-Help/Wks-SetColVal-MenuOpt-Dialog
This[SID]$ refers to current column's label row "SID"'s cell value (string type). |
| AKazak |
Posted - 10/31/2022 : 10:40:17 PM
quote:
Originally posted by cpyang
wcol(j) should not be used in cell formula, also, quoting sheet name should not be an issue, please try
=lookup(This[SID]$,"Data"!B,"Data"!X)$
wcol(j) is for using in multiple column formula where j will be properly incremented.
CP
Thank you for clarifying this.
Where do I read about pre-defined variables, including This?
In particular, what does This[SID]$ mean?
---
Andrey |
| cpyang |
Posted - 10/31/2022 : 8:14:19 PM
wcol(j) should not be used in cell formula, also, quoting sheet name should not be an issue, please try
=lookup(This[SID]$,"Data"!B,"Data"!X)$
wcol(j) is for using in multiple column formula where j will be properly incremented.
CP
|
| YimingChen |
Posted - 10/31/2022 : 12:13:44 PM
I will report to fix it. Can you use the syntax below instead?
=lookup(wcol(j)[D1]$,Book1_B,Book1_X)$
Here is a better solution by removing the quotation marks, try below:
=lookup(This[SID]$,Data!B,Data!X)$ |
| AKazak |
Posted - 10/31/2022 : 01:59:15 AM
The issue is still here.
Can you look at the formula in Group Lookup column label row in the following sample project, please?
https://my.originlab.com/ftp/forum_and_kbase/Images/Column%20Label%20Row%20Lookup%202022-10-31.opju
---
Andrey |
| AKazak |
Posted - 07/08/2022 : 01:56:28 AM
quote:
Originally posted by YimingChen
Thank you for your clarification. Yes, I can reproduce the issue. It doesn't work by setting formula.
To trigger the update, you may need to click the Refresh toolbar button or press F5.
James
Dear James,
Thank you for the suggestion.
Please ask the developer to fix the issue in the next beta version.
---
Andrey |
| YimingChen |
Posted - 07/07/2022 : 08:47:19 AM
Thank you for your clarification. Yes, I can reproduce the issue. It doesn't work by setting formula.
To trigger the update, you may need to click the Refresh toolbar button or press F5.
James |
| AKazak |
Posted - 07/07/2022 : 06:18:24 AM
Do you reproduce the issue?
---
Andrey |
| AKazak |
Posted - 07/02/2022 : 01:15:32 AM
quote:
Originally posted by YimingChen
To me RL formula (=wcol(j)[L]$) works but RL Cell (wcol(j)[L]$) doesn't work. Is that the same case for you? See the image below and the attached project file.
https://my.originlab.com/ftp/forum_and_kbase/Images/substitution_YM.opju
James
I see a misunderstanding here.
In my system:
if I put "=wcol(j)[L]$" in RL cell(s) manually (via cell edit), the it works as expected;
if I put "wcol(j)[L]$" in RL cell(s) manually (via cell edit), the cell(s) display "wcol(j)[L]$" as regular text as expected, since equal character "=" is missing.
This is fine.
My goal is to design RL formula and paste it via Edit GUI dialog, but it fails:
By the way, I noticed that LR formulas seem not to update automatically.
How do I trigger automatic updates of the LR formulas?
---
Andrey |
| YimingChen |
Posted - 07/01/2022 : 3:23:08 PM
To me RL formula (=wcol(j)[L]$) works but RL Cell (wcol(j)[L]$) doesn't work. Is that the same case for you? See the image below and the attached project file.
https://my.originlab.com/ftp/forum_and_kbase/Images/substitution_YM.opju
James |
| AKazak |
Posted - 07/01/2022 : 2:45:46 PM
quote:
Originally posted by YimingChen
Then there is a bug. Please use =wcol(j)[L]$. I don't see it's not updating. You may need to click the refresh button.
James
Dear James,
If I use =wcol(j)[L]$ as LR formula, then Origin does not accept it and leaves LR empty. Seems to be as a bug...
---
Andrey |
| YimingChen |
Posted - 07/01/2022 : 1:50:17 PM
Then there is a bug. Please use =wcol(j)[L]$. I don't see it's not updating. You may need to click the refresh button.
James
|
| AKazak |
Posted - 07/01/2022 : 12:46:31 PM
quote:
Originally posted by YimingChen
https://my.originlab.com/ftp/forum_and_kbase/Images/substitution.opju
Unfortunately, It doesn't work.
See the following updated project:
https://my.originlab.com/ftp/forum_and_kbase/Images/substitution_AK.opju
---
Andrey |
| YimingChen |
Posted - 07/01/2022 : 12:34:20 PM
https://my.originlab.com/ftp/forum_and_kbase/Images/substitution.opju |
| AKazak |
Posted - 07/01/2022 : 12:18:50 PM
quote:
Originally posted by YimingChen
Because the cell asks for a text, and =wcol(j)[L]$ is already a text, so no need of substitution notation. Or you can put ="%(wcol(j)[L]$)", which is equal to ="AA".
James
Not sure I've got your point.
Can you share a sample project, please?
---
Andrey |
| YimingChen |
Posted - 07/01/2022 : 11:54:25 AM
Because the cell asks for a text, and =wcol(j)[L]$ is already a text, so no need of substitution notation. Or you can put ="%(wcol(j)[L]$)", which is equal to ="AA".
James |
| AKazak |
Posted - 07/01/2022 : 11:37:09 AM
quote:
Originally posted by YimingChen
you need substitution notation %(wcol(j)[L]$) for text. try the line below:
=Lookup(%(wcol(j)[L]$), Sheet1!A, Sheet1!B)$
Hasn't got your point.
If I put %(wcol(j)[L]$) to LR formula, then it returns "--" for all the LR cells (see the screenshot above).
What LR formula will return long names of the corresponding columns?
---
Andrey |
| YimingChen |
Posted - 07/01/2022 : 10:33:40 AM
you need substitution notation %(wcol(j)[L]$) for text. try the line below:
=Lookup(%(wcol(j)[L]$), Sheet1!A, Sheet1!B)$
|
| AKazak |
Posted - 07/01/2022 : 02:49:15 AM
quote:
Originally posted by AKazak
Why does the user parameter formula =$(wcol(j)[L]) returns "--" if the corresponding long name contains a string instead of a number?
Thank you.
---
Andrey
I read the following useful references on the topic:
https://www.originlab.com/doc/en/LabTalk/ref/Column-Label-Row-Characters
https://www.originlab.com/doc/LabTalk/ref/WCol-func
How I couldn't find the root cause of the issue.
I discovered that wcol(j)[L]$ works fine if put in each LR cell, but doesn't work at all if I put wcol(j)[L]$ to LR Formula:
Is this a software bug?
---
Andrey |
| AKazak |
Posted - 07/01/2022 : 12:08:53 AM
Why does the user parameter formula =$(wcol(j)[L]) returns "--" if the corresponding long name contains a string instead of a number?
Thank you.
---
Andrey |
| AKazak |
Posted - 06/30/2022 : 2:27:19 PM
quote:
Originally posted by AKazak
I want to use lookup function. How do I treat Categorical columns at the lookup reference sheet?
---
Andrey
The following code seems to work fine:
=Lookup($(wcol(j)[L]), Sheet1!A, Sheet1!B)$
However I noticed that the formula in a User Parameter doesn't not update automatically if I alter Long Name values.
Is there a way to turn automatic updates on?
---
Andrey |
| AKazak |
Posted - 06/30/2022 : 2:20:39 PM
quote:
Originally posted by YimingChen
=list(wcol(j)[L], book2_B)
the formula finds the value of the column longname in [book2]1!col(B), and returns the row index.
James
Dear James,
Thank you for the suggestion.
I want to use lookup function. How do I treat Categorical columns at the lookup reference sheet?
---
Andrey |
| YimingChen |
Posted - 06/30/2022 : 09:37:55 AM
=list(wcol(j)[L], book2_B)
the formula finds the value of the column longname in [book2]1!col(B), and returns the row index.
James |
