Skip to main content

Hello,

I have 2 Transaction Lists :

  • List 1 : with Doc # (string), Parent Item (Dimension), Product Lvl 2 (Dimension)
  • List 2 : with Doc # (string)

None of the Doc # is unique on both Lists.

In List 2 I want to retrieve Parent Item following the rule : IF List1.Doc # = List2.Doc # AND Product Lvl 2 = “VPS” Then Parent Item (Dimension)

I tried this but doesn’t work and I don’t understand why. 

 

In my screenshot, P&L.Transaction = Doc # from List 2 and Transaction List.Document Numer = Doc # from List 1

Thanks for your help !

 

Alexandre

Hi Alexandre,

I was recently working on a similar use case, and found a formula that may be working well.
If your 'List 1'.# and 'List 2'.# do match, you could use the ITEM function to get there.

The documentation specifies the second argument should be a unique property, but it does work (with less predictability though) - and you can use it to find an item in a transaction list too, even though it can’t be in your end result.

 

So I’d try in List 2 to type something like

ITEM(
   'List 2'.'#',
   'List 1'
        FILTER: 'List 1'.'Product Lvl 2' = Product."VPS"]
       .'#'
   ).'Parent Item'

 

What this formula does:

  • find the first transaction that matches both #
  • matches only on list 1 items that have Product Lvl 2 = VPS
  • chain dimensions to get the List 1’s Parent Item

This should be the most efficient solution, although the formula is indeed complex.

Let me know if that solves your issue - it’s an interesting problem!

Wow this is amazing ! Works very well, thank you very much ! 😀

Reply


Ask your question here!

Terms & ConditionsCookie settings