JOIN COLLECT two variables

gwson

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

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.