Select value from future transaction

fredrickstraube

Hi,

I have a module with a number of transactions for different entities. For all of these entities, I have a Unique ID (UID) and a valid from date. I also have a line item that uses the RANK function to rank the transactions. See picture below.

 

Now to the issue; I need a value in the Valid To line item. This value should be the same as the Valid From for the next transaction. For example, The Valid To for the row #5721 should be 3/27/2019. 

My initial idea was to do something like this: Valid To = Valid From [LOOKUP: UID, LOOKUP: Rank order+1] but I didn't manage to get that to work.

Does anyone know how to tackle this issue? Any help is appreciated!

Thanks a lot!

fredrickstraube

Hi Vignesh,

Thanks a lot for your answer! It is really close to what I need, there is just one thing missing:

In my original module, I have several different object, hence why I have a line item called UID. In the print screen I added, I had a filter to show just one UID. Somehow, I need to incorporate that into your solution. How would you do that?

See my results in the image below:

As you can see, I have a few different UID's but they all get the same result...

Thanks!

VIGNESH.M

Hi Fredrick,

 

Try this solution, but you need to create 1 list and 2 modules.

 

Step 1: Create a Rank List as shown below

 

Step 2:  Create 3 New line item(Next Item, Rank List & Next Rank List) and format the line item as shown below

 

Step 3: Create a module as Rank Mapping 1 and Add line item as Rank

            Applies To: #Transaction List and Rank List

            Format: #Transaction List : Summary: FIRST NON BLANK

            Use the below formula

 

Step 4: Create a module as Rank Mapping 2 and Add line item as Rank

            Applies To: Rank List

            Format: #Transaction List : Summary: NONE

            Use the below formula

Step 5: Write the formulas now and check the result.

 

If you find any other alternate solution please pass it here.

 

Regards,

Vignesh M

VIGNESH.M

Hi Fredrick,

 

If you want to apply this rule only for the set of data, then apply the rank only to the transaction which meets the criteria.

 

In my below example, I want to only apply the rule to UID- #5 data sets, so created flag line item and right a logic to flag only the records which you want and include that flag condition with Rank.

 

Correct me if I understood differently the requirement.

 

Regards,

Vignesh M

ArunManickam

Hello @fredrickstraube 

Since your rank is by UID. On top of @VIGNESH.M  solution, in his Rank Mapping 1 & 2 modules, you need to add UID as a dimension. Then you need to lookup for Next rank and lookup for the same UID.

 

Thanks

Arun

fredrickstraube

 Hi Arun,

Thanks a lot for you answer. How would I get the mapping modules to take the UID into account after adding the additional dimension? In my original model, the UID is a list formatted line item.
Thanks!

/Fredrick 

ArunManickam

Hello @fredrickstraube ,

 

Use this, 

Transaction-Rank summary type is First Non Blank

 

 

Thanks

Arun

premkumar12

If the Transactions list having parent hierarchy the line item "Transcation Rank" it's asking for top level after giving the top level to that list, after that also its showing "Built in top level not present" like this error i got..

 

 

Pls give any suggestions