Expand my Community achievements bar.

SOLVED

Select top 3 records of a column using list functions

Avatar

Level 2

Hi, whats the best way to identify the greatest 3 values in a column using 'list of functions'?

Regards

Ross

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hello rossc47800017​,

If you have finite scores and they are predefined you can do following:

Test data

1487023_pastedImage_3.png

1) create column for every score in enrichment per customer

   using IfEquals(score,'1' , '1','')

1487032_pastedImage_12.png

Result:

1487036_pastedImage_13.png

2) add another enrichment where you aggregate into one record

You can use any function for your scores (MAX,MIN) just for aggregation to work

1487024_pastedImage_4.png

Result

1487037_pastedImage_14.png

Hope this is close to what you need,

Marcel

View solution in original post

5 Replies

Avatar

Community Advisor

Hello,

I do not think so you can do it with "list of functions". But after your query activity add split.

1) in Record count limitation choose "Limit the selected records" option click edit

1484641_pastedImage_3.png

2) Keed the first ones after sorting

1484639_pastedImage_0.png

3) choose column and order

1484642_pastedImage_4.png

4) Choose size of records to keep

1484643_pastedImage_9.png

Hope this helps,

Marcel

Avatar

Level 2

Hi Marcel, thanks for this that did work!

I will admit I like to figure stuff out on my own through trial and error, however, I am struggling with this one. I think it's best if I provide a little more context about what I'm actually trying to do.

This is all about personalisation blocks in the template and where a value equals something they get a particular block in priority order, top down. Those values are coming through in a file, where a contact has duplicate rows and on each of those rows is a score. As discussed previously I want to pick up the top three scores, however, that leaves me with three rows per contact. I need to then transpose those values from a column to a row so that I can pass through one row per contact. This is why I was initially looking at using a function.

Any further thoughts?

Regards

Ross

Avatar

Correct answer by
Community Advisor

Hello rossc47800017​,

If you have finite scores and they are predefined you can do following:

Test data

1487023_pastedImage_3.png

1) create column for every score in enrichment per customer

   using IfEquals(score,'1' , '1','')

1487032_pastedImage_12.png

Result:

1487036_pastedImage_13.png

2) add another enrichment where you aggregate into one record

You can use any function for your scores (MAX,MIN) just for aggregation to work

1487024_pastedImage_4.png

Result

1487037_pastedImage_14.png

Hope this is close to what you need,

Marcel

Avatar

Level 2

Hi Marcel, thank you for this.

Yes, this is what I needed and with a bit of trial and error, I got there!

Thank you, greatly appreciated.

Regards

Ross

Avatar

Community Advisor

Mark as answered if so it would be appreciated

thank you..