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?
-
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.
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!