Dynamically referecing columns in formulas
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
My first post here so I apologize in advance if broke Labels/Posting Rules.
I have a column (named Field) that contains other column names. I want to dynamically reference said columns in a formula.
I tried using double brackets: [[Field]] but that won't work.
Is there a special syntax to bypass this ?
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Do you have a sample example? @AmineB
Yugandhar Muley (Yug)
Alteryx ACE | Alteryx For Good India Lead | Bengaluru Alteryx User Group Lead
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
I'm not allowed to upload anything so I'll try my best to retranscribe the table.
Col A | Col B | Col C | Field | Value |
1 | 10 | 100 | Col A | Yes |
2 | 20 | 200 | Col B | Yes |
3 | 30 | 300 | Col C | Yes |
1 | 10 | 100 | Col A | No |
2 | 20 | 200 | Col B | No |
3 | 30 | 300 | Col C | No |
Basically, I want to do a formula :
IF [Value] = "Yes" THEN
IF [[Field]] = 10 THEN "Message 1" ELSE "Message 2" ENDIF
ELSE
IF [[Field]] = 10 THEN "Message 3" ELSE "Message 4" ENDIF
ENDIF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It is still difficult to comprehend your expected output or the formula that you have written.
I have written down something like this, is this what you are looking for?
If not, instead of writing the fomula, please just tell me the expected output (dont write formula) just tell what you are looking to find within the data set.
Yugandhar Muley (Yug)
Alteryx ACE | Alteryx For Good India Lead | Bengaluru Alteryx User Group Lead
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for your patience.
The condition is based on the Field column which contains in my dataset +130 different variables (each variable is named after an already existing column)
In the example I made I can make a switch statement for 3 different values. But for +130 it's more complicated. I'll try to make a different example:
COL 1 | COL 2 | COL 3 | COL 4 | COL 5 | COL 6 | Field |
1 | 34 | 66 | 98 | COL 1 | ||
1 | 34 | 66 | 98 | COL 2 | ||
1 | 34 | 66 | 98 | COL 3 | ||
1 | 34 | 66 | 98 | COL 4 | ||
1 | 34 | 66 | 98 | COL 5 | ||
1 | 34 | 66 | 98 | COL 6 |
My goal is to check whether each one is empty or not, I apply a formula :
IF IsEmpty([[Field]]) THEN "Yes" ELSE "No" ENDIF
I was hoping that [Field] would be read as COL 1 and the I have the second brackets so it be treated as [COL N]
To get something like this:
COL 1 | COL 2 | COL 3 | COL 4 | COL 5 | COL 6 | Field | Result |
1 | 34 | 66 | 98 | COL 1 | No | ||
1 | 34 | 66 | 98 | COL 2 | Yes | ||
1 | 34 | 66 | 98 | COL 3 | No | ||
1 | 34 | 66 | 98 | COL 4 | No | ||
1 | 34 | 66 | 98 | COL 5 | Yes | ||
1 | 34 | 66 | 98 | COL 6 | No |
The ultimate goal is to have for each row tracking each column whether it's empty or not.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@AmineB Out of the Box the Dynamic Replace is the tool to use for something like this. I've attached an example. I believe there is a Crew Macro if you use those. I also have an idea out there of adding an eval function which would make this much more straightforward.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@ patrick_digan Hello, thank you for the reply.
I agree with you, I believe I'm looking for something similar to the eval function. Unlucky it's not yet implemented.
For security reasons, I can't use macros.
I'm checking out the Dynamic Replace and it seems the right track for me. So thank you.
Have a good one 🙂
