Adding "and" after last delimiter in list/string

[kmstephenson]

Hi all,

How do I add an "and" after the last delimiter comma within a list? I will mention, it is possible that the variable contains a comma.

='The list includes '&CONCAT(DISTINCT variable,variable&', ')

For example:

variable

ABC

DE - F

GHI, JK

LMN

OP QR

etc.

I would like this to display with a ", and" for the last value.

For example, if the first 3 rows are possible based on selections, it would display as:

ABC, DE - F, and GHI, JK

If all rows were possible, it would display as:

ABC, DE - F, GHI, JK, LMN, and OP QR

Thanks!

[Arthur_Fong]

Try this:

Add a new column to order your list at script level.

Then create a variable to store the max rank of the list.

Use this as expression:

='The list includes '&CONCAT(DISTINCT if(aggr(max(Order),Order)<vMaxRank,variable),',')&', and '&
CONCAT(DISTINCT if(aggr(max(Order),Order)=vMaxRank,variable),',')

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

[kmstephenson]

Thanks for the response! Is it possible to complete this without having to complete the LOAD INLINE/ORDER? The example I posted with the variable and 5 values is much simpler than the actual variable - it includes many more values that may change as the data is updated so maintaining a hardcoded LOAD INLINE would be challenging. Thanks!!

[Arthur_Fong]

This is just a sample app.

You have to load your data from your data sources.

[Arthur_Fong]

Raw:
LOAD * INLINE [
variable
ABC
DE - F
GHI, JK
LMN
OP QR
];        <<<<<change this to your load script from your data source.

//Define order
Data:
load *,
if(RowNo()=1,1,peek(Order)+1) as Order
resident Raw;    <<<<This will work fine after you have changed the above inline table

EXIT SCRIPT;

[kmstephenson]

Ahhh, sorry I misinterpreted that as to be used for load order. Can you explain what the following is ordering and how it is ordering?

if(RowNo()=1,1,peek(Order)+1) as Order

The dataset that contains my "variable" contains multiple fields - and "variable" values are replicated across rows. I would like the list to be ordered in alphabetical order - even though these values may not be ordered this way in the data. Does that make sense? Should I still use the logic above?

[Arthur_Fong]

Yes it is ordering in alphabetical order.

[Arthur_Fong]

You can add in order by clause after resident:

//Define order
Data:
load *,
if(RowNo()=1,1,peek(Order)+1) as Order
resident Raw

order by [Column1],

                   [Column2] etc;

[Brett_Bleess]

Kristina, it seems Arthur has provided quite a bit of detail that likely helped you get a working solution?  If so, please be sure to return to the thread and use the Accept as Solution button on any of Arthur's posts that helped you get things working as you wanted.  If you are still working on things, please leave an update with what you still need.  Marking things gives credit to those helping you, and it lets other Community Members know what actually worked...

Regards,
Brett

[sunny_talwar · Accepted solution]

This might be another approach without using Aggr() function and just manipulating the result of Concat

='The list includes '& Left(CONCAT(DISTINCT variable,','), Index(CONCAT(DISTINCT variable,','), ',', -1)) & ' and ' & SubField(CONCAT(DISTINCT variable,','), ',', -1)