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

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

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?

gwson

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.

Paul Newcome

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.

gwson

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.

Paul Newcome

Is it possible to have to two columns combined in the source BEFORE it is grabbed by the data uploader?

gwson

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.

Paul Newcome

That would certainly be the easiest way for the SS portion to work.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.