parse out row values into one column?

[1600eads]

I'm looking for something similar to sql's listagg or concat_ws. Looking to parse out values from one column and list it out on another column delimited by a pipe ( ' | ' ).

In this case, I'd like to do this for both my date and model column.

My Data:

name	model_id	date	model
Jason	12	12/25/2023	BXC85
Jason	13	12/25/2023	XKL95

Desired out: *planning to drop the model_id so that it doesn't aggregate and return 2 rows

name	model_UPDATED	date_UPDATED
Jason	BXC85 | XKL95	12/25/2023 | 12/25/2023

Greatly appreciate any feedback or guidance on how to replicate the desired output above.

[rwunderlich]

The function is Concat()

Concat(model, ' | ')

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/StringAgg...

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/StringAggregat...

-Rob

[1600eads]

Thank you for the quick feedback, very much appreciated. Is there a way to make this a dimension so that end users could filter on the column as a dimension behaves?

edit: I was able to use aggr to make it selectable.

One final question I have - Noticed in some cases, my dates are not align with the model

Example - 

name	model_UPDATED	date_UPDATED
Jason	XYZ199 | ABC555	11/25/2023 | 01/25/2023

The model value XYZ199 should correspond to the 01/25/2023 date, not sure how this can get controlled. Initially thought of a match and doing a DESC/ASC but that would be manual. Are there any tricks to ensure the newly created dimension [model_UPDATED] corresponds to its correct [date_UPDATED] values? Thanks again.

[rwunderlich · Accepted solution]

Use the model_id field as the sort_weight parm in both concat(). Then they should align.

Concat(model, ' | ', model_id)

Concat(date, ' | ', model_id)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[1600eads]

Much appreciated, this did the job!

One final question for the forum - 

In the example of this data:

name	model_UPDATED	date_UPDATED
Jason	XYZ199 | ABC555	11/25/2023 | 01/25/2023

If I wanted to do a rank on the date_UPDATED field to only output the max date (in this case, 11/25/2023) how would one go about this? In SQL, I can run a subquery and use a rank and set it DESC but ideally would like to handle this on the Qlik side either through the data load editor and/or chart expression. Thank you again.

[rwunderlich]

Date(Max(date))

-Rob