Forum Discussion
Helper IDAX If Vlookup Statements
Hi HB2104,
Try a calculated column like this:
And in plain text for convenience:
New Calculated Column = VAR _CurrentCoCd = [CoCd] VAR _CurrentCurrency = [Currency] RETURN COALESCE ( MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && [Currency] = _CurrentCurrency ), [Bank Info] ), MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && CONTAINSSTRING ( [Currency], "ALL OTHER" ) ), [Bank Info] ) )Best Regards,
Alexander
Thank you, awesome! I assume Tb12 in this is the table for the bank numbers?
Yup, it's the second table with the columns [Company], [Currency] and [Bank Info]. In fact, it's "Tbl2", short for "Table 2". 🙂
Thank you so much! Does it matter about the columns in these being "text" vs "number"? Like lets say I had the bank info and it wasn't just numbers it also had letters in it as well.
There shouldn't be any problems with text.
- Works great, thank you!
Solution SageSorry, but I don't understand the logic here and the role of Calendar Rules in your calculations.
Why is there no Tuesday and LOMB in the second table?
Could you please re-explain in more details how it should work?
Helper IThe calendar rules are the work days that they do Accounts Payable payment runs, in this case they only run payments on Monday, Wednesday and Fridays. So if the due date is on 9/19/23, SAP will pick this invoice up Monday the 11th.
In that second example, the due date is 9/15/23 and 7 days prior to that would be 9/7/23 but that falls on a Thursday and SAP won't pick it up since it's not a payment run day. So the answer would be the following day, based on the calendar rules Friday the 8th it would be picked up for payment. Super complex and unsure a formula can be created for that logic. 😅
- barritown
Are these Mon-Wed-Fri paydays applicable for all the companies? Otherwise, why are there no lines with "LOMB" in the second table?
- HB2104
LOMB has a payment run every day of the work week, I should have put that in there. There are a few others outside the sample I provided that are different such as just a payment run on Tuesdays.