I have 3 vendors

Vendor A costs $300

Vendor B costs $800

Vendor C costs $1,000

How do I establish Rank in the data layer?

Vendor C, $1,000, Rank 1

Vendor B, $800, Rank 2

Vendor A, $300, Rank 3

Next question..... 

How do I only do this for rows where a field matches a particular string?

example:

10 vendors.... only 5 have managed services agreements. I only want to rank those. 

Yes, I want to do this in the data layer because I will have to model it to enable trending in KPIs. 

This may be done in two parts (probably a better way of doing it)

Firstly is there a way you can identify what vendors have managed services agreements? 

Secondly you could try a table match and then rank the vendors in there - see the recent challenge for reference: Challenge 2: Detect most recent server location and status [+] 

Hi @Matthew Poletiek

If you want to rank those 3 vendors based on information that you have on another column, you can use an If Statement and create a new column. You can also modify the raw data, or upload a new data table where the rank of those vendors is contained and either use a Join Step or a Vlookup statement from that table. If that classification depends on several other columns, you can nest if statements on one another or use a Table Match function just like in the Challenge @Julie Batty shared.

For your second question, you could either classify them in that table or in another one and use one of these solutions, or in the case that you wont need the data from those vendors that do not match "Managed Service Agreements", use a Filter Step.

Thankfully I only needed the top 3. 

What I ended up doing was finding MAX(), flagging the rest and if flagged find MAX() again, flagging the rest, if flagged MAX().

Lots of step columns. Not a great solution for ranking 100+ rows.

We have a similar need for the Rank() function. In our case we're ranking capabilities, BUs, apps by infra spend, dev spend, dev hours by type and total.  For about a year we've been downloading and transforming 8 datasets in order to rank them in excel and them re-upload. It'd be a lot simpler if we could get a Rank function. @Sandra Daniels

I have this same question but on the report surface. I can added a formula table with =Row()+1 but the answer returned by row does not take into account the sort by value applied to the table so my most expensive item (rank 1) is actually 80 in my formula as I think ROW is returning the unsorted table rows. I can’t find a suitable solution. Note rank could change with time period as I’m (sorting table) ordering data by cost YTD. 

For longer lists I ended up using the API to download a table from the report surface, process it in a script, and re-upload it with the rows ranked. 

The file generated is sucked in via a Filesystem Datalink Connector.