Does Aperture have "rank()" function and how it works?

Eekki
edited December 2023 in General

Hi,

is there similar function for Aperture?

For example case


Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited September 2021

    What are you trying to achieve?

    Is it to have values with the same score given the same 'rank', i.e. both competitors getting a silver medal?

    Is it to order within a group of matching records?

    Something else?

    Also, do you need the rank numbers 1,2,3... or just the data in the correct order? If you can manage without the values 1,2,3 then getting the data in the correct order is simple using sort step on multiple columns.

    There is a Custom Step developed using the SDK called Partition which supports rank. Your Experian contact can share more details and possibly the step with you to see if it meets your requirements.

  • Hi,

    sorry for the bad start intro to the topic.

    I try to arrange the data so that I can get the contract numbers of the phone number in ascending order and I can see the number of contracts per number and if necessary select the number of the last contract.

    For example:

    phone_number, contractid, opened, closed, rank (rank phone by contractid)

    09-123456, 10003, 2021-05-01, 2021-06-30, 1

    09-123456, 10002, 2021-01-01, 2021-04-30, 2

    09-123456, 10001, 2020-01-01, 2020-12-31, 3

    09-777777, 10012, 2021-01-01, 2021-06-30, 1

    09-777777, 10011, 2021-01-01, 2021-06-30, 2

    09-999999, 10013, 2021-01-01, 2021-06-30, 1

    09-999999, 10012, 2021-01-01, 2021-06-30, 2

    09-999999, 10011, 2021-01-01, 2021-06-30, 3

    09-666666, 10014, 2021-01-01, 2021-06-30, 1

  • Sami Laine
    Sami Laine Contributor
    edited September 2021

    If you want just to see how many contracts there is for each phone number and get their last contract then I would do a following workflow.

    • Sorts the table in ascending order of phone numbers and/or why not by date? It depends what you want the last ID to be.
    • Groups the contracts by phonenumber.
    • Add aggregates to grouping a) Count of Records (this is set by default, you could rename it), b) Last Value of ContractID.

    You could then join the counts and last values back to the original records if you want to preserve duplication.

    Is there any need for actual rank number? If you really want to see all the records individidually side by side like in your example and have numeric rank to each one of them - I think there was a function for that in Pandora, but I have not found it in Aperture. It added +1 each time from record to record by some other grouping just like you show.

    If its truly needed and nobody can tell the actual function for this, there is a way to do this.

    • Add an aggregation that contains 'all group values' of the ContractIDs for the above phone number.
    • Join these aggregates and grouped IDs back to original set of data.
    • use transformation to get all text BEFORE the current row ContractID from the above list of all ContractIDs. You remove everything from the end of the list.
    • Use transformation called List-Count to check how many values are left.

    In the end, you will have a rank i.e. position number for each of the records in their group. This might not be computationally good idea for large data sets but could be used if no better function exists.

  • Josh Boxer
    Josh Boxer Administrator

    Firstly, use Group step to group by phone number and (i assume last contract = last by close date) Max close date

    Then use a Lookup step to match the grouped data to the initial source data

    You can define additional columns in the Lookup step to Count contracts per phone number or even to return all contract_ids in a single column