sequential numbering in line item

Jsdeloria21
Jsdeloria21
in Modeling

Hi,

I would like to know how can we create a sequential numbering in a line item. I saw some post where they use RANK but it does not fit the requirement needed. 

 

In the module attached, I have a line item Transaction number where I want to show automatic numbering (1 - 100 for example) of the number of transactions. This will be used once it is imported to another model where there is a numbered list present. Is there a formula that will automatically show the numbers on the line item.

 

Thanks in advance

 

Regards,

 

 

Tagged:

Comments

  • BenHilgendorf
    BenHilgendorf

    I'm not sure what post you were looking at, but I was able to use RANK to solve the problem. Steps are as follows:

     

    (1) Create two new line items in TAT_HRApplications: Start Date of Month, Rank Grouping.

     

    (2) Start Date of Month will be a Date-formatted line item with the formula START(ITEM(Time)). This will create a tie on transaction rows for a given month and applicant, allowing for a ranking based on a sequential tie-breaker. 

     

    (3) Rank Grouping will be a List-formatted line item (where the List is your Applicants, shown in the source module's page selector) with the formula ITEM(*insert name of Applicants List here*). This will be used to "restart" the ranking for every applicant. 

     

    (4) In your transaction count line item, enter the formula RANK(Start Date of Month, ASCENDING, SEQUENTIAL, TRUE, Rank Grouping). The second argument is arbitrary. 

     

    (5) Map transaction count to your target module's list upon import.

     

    Best practices tips: (1) The two new line items can be pared down to their respective dimensions (Start Date of Month only applies to Time, and Rank Grouping only applies to Applicants List) to reduce module size. (2) If these calculations have the potential to be used in other modules, I suggest splitting these out into modules and referencing them in your Applicants Module to avoid cluttering one module with multiple subsidiary views. From my understanding, subsidiary views decrease model performance (not to be confused with model size). 

     

    Let me know if this works for you.

  • paul.rosal
    paul.rosal

    You can use the formula below in a line item to generate a sequential number.  This works best when you only have one List in your module and the numbering follows the order of that list.

     

    RANK(1, DESCENDING, SEQUENTIAL)

     

    Your solution to go from Source to Target seems odd since it uses an arbitrary sequential number. This is not guaranteed to maintain the original order relative to the loaded data since the primary List could change.

  • tbeck
    tbeck

    If you create a Numbered List and import a multi-dimensional module - to create a flat list -  using list properties, you can use the list index to creaste a numbered list, by using what I call the "Rank Value Right" funciton.
    1) Create a List Property and Format to a Number. 
    2) Input the following logic: RANK(VALUE(RIGHT(NAME(ITEM('Numbered List')), LENGTH(NAME(ITEM('Numbered List'))) - 1)))

    This logic also works in modules with a single Numbered List Applied to it. Only in a module I would suggest splitting the logic in multiple line items, but for a list, generally delaing with exports, one line is ideal for delivery. 

Categories