JOIN COLLECT two variables
I am trying to find a way to collect two different variables from two different ranges {courseyear} and {regstatus}, then join them together to determine the year and registration status in a single cell:
Here is what I would like:
=JOIN(COLLECT({courseyear}, {id}, $ID@row, ({regstatus}, {id}, $ID@row)), "-")
Output: 2020-R, 2021-R,2022-W
Answers
-
Are you able to provide a screenshot of your source data with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
PS. I am able to do individual queries such as:
=JOIN(COLLECT({courseyear}, {id}, $id@row), ",") to return 2020,2021,2022 for ID 1
=JOIN(COLLECT({regstatus}, {id}, $id@row), ",") to return R,W,R for ID 1
but I need them to be together like on the picture, and I am not sure how to do that.
-
You are going to need to use a "helper" column (text/number type) to join those two columns on a row by row basis.
=courseyear@row + "-" + regstatus@row
Then you can run your JOIN/COLLECT on the helper column.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks Paul, I will keep the helper column in mind. The trick for me that is that the entire database gets replaced once a day by using the data uploader so the helper column would be deleted. I will keep researching.
-
Is it possible to have to two columns combined in the source BEFORE it is grabbed by the data uploader?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That's an idea, I will check with our intergration team to see if they can adjust the database report to include a concatenated values in a helper column.
-
That would certainly be the easiest way for the SS portion to work.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!