Solved

# Matching 2 Transaction Lists without unicity

• 2 replies
• 66 views

Userlevel 3
+5
• 29 replies

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

Alexandre

icon

Best answer by francois 23 March 2023, 17:42

View original

### 2 replies

Userlevel 6
+7

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!

Userlevel 3
+5

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