LOOKUPVALUE gives 'A table of multiple values was supplied where a single value was expected.'

[pkruarat]

Hello,

Currently, my data table has 'main' contracts (e.g. 328487) and also 'supplement' contracts (e.g. 328488), which can have many rows and are linked together in Column LinkRef1. I would like to create a new column to lookup MaturityDate of the supplement contracts to check if it is the same as the main contract:

MDCheck = IF(Table[MaturityDate]=LOOKUPVALUE(Table[MaturityDate], Table[ContNo], Table[LinkRef1]), "Yes", "No")

This seems to give an error message 'A table of multiple values was supplied where a single value was expected'.

Ultimately, I would like to create this check for ValueDate, MaturityDate, and PrincipalFace. What would be the best solution for this?

Any help would be highly appreciated. Thank you!

Sample data:

ContNo	LegNumber	LegType	LinkRef1	TradeDate	ValueDate	MaturityDate	Original_Curr	PrincipalFace
328488	0	Pay	328487	Tue, 29/Aug/23	Thu, 31/Aug/23	Thu, 28/Nov/24	EUR	10000000
328488	0	Receive	328487	Tue, 29/Aug/23	Thu, 31/Aug/23	Thu, 28/Nov/24	EUR	-10000000
328487	0	Invest	328488	Tue, 29/Aug/23	Thu, 31/Aug/23	Thu, 28/Nov/24	EUR	-10000000

[azakir]

Hi @pkruarat 

Seems like you're trying to lookup a value from a column that has multiple values (ideally there should be one). 

See if this formula helps you in any way: 

Example =

CALCULATE (

    FIRSTNONBLANK (Table[MaturityDate], 1 ),

    FILTER ( ALL (Table), Table[ContNo] = Table2[LinkRef1])

)

[pkruarat]

Hello @azakir ,

Thank you for your suggestion! But, this seems to return just the lowest value/earliest date in the MaturityDate column for all rows.

[ryan_mayu]

what's the expected output based on the sample data you provided?

[pkruarat]

Hello @ryan_mayu ,

The expected output is Yes for all 3 lines, please, because MaturityDate of 328488 and 328487 are the same.

I understand that the problem is that there are 2 lines for 328488. Is there a way to check against the first available matched value? They are matched through Column LinkRef1.

ContNo	LegNumber	LegType	LinkRef1	TradeDate	ValueDate	MaturityDate	Original_Curr	PrincipalFace	MDCheck
328488	0	Pay	328487	Tue, 29/Aug/23	Thu, 31/Aug/23	Thu, 28/Nov/24	EUR	10000000	Yes
328488	0	Receive	328487	Tue, 29/Aug/23	Thu, 31/Aug/23	Thu, 28/Nov/24	EUR	-10000000	Yes
328487	0	Invest	328488	Tue, 29/Aug/23	Thu, 31/Aug/23	Thu, 28/Nov/24	EUR	-10000000	Yes

[ryan_mayu · Accepted solution]

@pkruarat 

maybe you can try this

Column = if(ISBLANK(maxx(FILTER('Table','Table'[ContNo]=EARLIER('Table'[LinkRef1])),'Table'[ContNo])),"No","Yes")

[pkruarat]

This solved my problem! Thank you so much!

I'll use this to check other values as well 😃

[ryan_mayu]

you are welcome