cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
domc23 Regular Visitor
Regular Visitor

Looking up vlaues in seperate tables

Hello, I am new to the forum and I will try to explain what I am looking to do the best way I know how.

I have two data tables, one called Claims and the other called RBP. On the Claims table, there is a column called Client Ref# and also a column called AllowedAmt. On the RBP table, there is a column called Claim Number and a column called Allowed Amount. What I am hoping to do is have a query look to match the Client Ref# on the Claims table to the Claim Number column on the RBP table and if it finds a match to then compare the AllowedAmt column on the Claims table to the Allowed Amount on the RBP table. If those amounts match, then report back a "Yes". If they do not match, then report back a "No".

Ultimatly, I am looking to see if the allowed amounts are the same or different for the same claim number across the two tables. I hope this makes sense. 

Any direction with this is greatly appreciated.

 

 

11 REPLIES 11
Super User
Super User

Re: Looking up vlaues in seperate tables

So, you would likely want a relationship between those two tables and then in your Claims you could use a custom column with a RELATED statement to check if the amount is greater than the related amount in your other table. Sample data would help get to a more specific solution.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

Thank you for your response. Let me get you some sample data and see if that will help explain what I am looking to do a little better.

domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

Here are the sample data from the two tables. Sorry about how this is viewing, I need to learn how to post samples.

The hope is to compare Claim Ref#  and the AllowedAmt from Table1 to the Claim Number and Allowed Amount from Table2. I have added a new column with a Yes or No value depending on what the query finds. IF there is a match on a claim number AND there is a match on the allowed amount from both tables, then I would like to see a Yes value retured. If there is a match on a claim number and the allowed amounts DO NOT match, them I would like to see a No value returned.

 

This probably does not help you at all but hopefully it will shed a little more light on what I am attempting to do. If you know of a simplier way to to accomplish this, please let me know.

 

Thank you in advance.

                                                                                      

Table 2
Claim NumberAllowed AmountAllowed Amount Match
00486JKL000001 $                       1.01Yes
00486JKL000002 $                     13.34No
00486JKL000003 $                     45.67Yes
00486JKL000004 $                     98.76Yes
00486JKL000005 $                     13.22Yes
00486JKL000006 $                     54.76Yes
00486JKL000007 $                     29.00Yes
00486JKL000008 $                     55.87Yes
00486JKL000009 $                     60.74Yes
00486JKL000010 $                     65.61Yes
00486JKL000011 $                     70.48Yes
00486JKL000012 $                     75.35Yes
00486JKL000013 $                     80.22Yes
00486JKL000014 $                     85.09Yes
00486JKL000015 $                     92.45No

                                                                      

Table 1
Client Ref# AllowedAmt 
00486JKL000001 $              1.01
00486JKL000002 $           12.34
00486JKL000003 $           45.67
00486JKL000004 $           98.76
00486JKL000005 $           13.22
00486JKL000006 $           54.76
00486JKL000007 $           29.00
00486JKL000008 $           55.87
00486JKL000009 $           60.74
00486JKL000010 $           65.61
00486JKL000011 $           70.48
00486JKL000012 $           75.35
00486JKL000013 $           80.22
00486JKL000014 $           85.09
00486JKL000015

 $           89.96

 

Super User
Super User

Re: Looking up vlaues in seperate tables

Can't figure out why this works, seems like the greater than/less than is opposite of what it should be.

 

Allowed Amount Match = IF([ AllowedAmt ] >= RELATED(RDB[Allowed Amount]),"Yes","No")

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

Interesting!

 

How is this doing a claim reference by claim reference comparison first to find a match to then go and look at the allowed amounts?

 

 

Super User
Super User

Re: Looking up vlaues in seperate tables

Hi @domc23,

 

Assuming there will never be duplicate Client Ref# on Table 1, first create a relationship from Client Number on Table 2 to Client Ref# on Table1.  Then the suggested formula should work.

domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

Thank you for your response.

I have already created the releationship between the two tables. There should not be a dublicate claim number in table 1 so we should be good. I am using a Attribute Slicer to control two seperate graphs. That seems to work just fine. When I select a claim number from that slicer, both graphs will only display that claim number. The issue I am having is I have setup another slicer to display the Yes or No results from the above formaula and when I select either, only the table with that column that was added (Table1) will display the correct claim numbers. Table2 will not adjust to display the correct Yes or No selection. I am looking to have the Yes - No slicer control both graphs that I have setup.

Super User
Super User

Re: Looking up vlaues in seperate tables

Hi.

 

In that case, you must create a Yes,No column in Table1 as well using the LOOKUPVALUE() function.  Then you will have to create another table (Table3) with only two values - Yes and No.  The Yes/No columns of Table1 and Table2 will need to have a relatioship to Table3.  In the slicer, you will drag the Yes/No column from Table3.

domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

Thank you Anish,

 

I am trying the Lookupvalue function but it is saying I need 3 arguments for it to work. I entered the following into table2.

Results = LOOKUPVALUE(ClaimsSLAReport[Client Ref #],ClaimsSLAReport[Allowed Amount Match])

 

With the Client Ref# and Allowed Amount Match being on Table1,

 

The hope would be to have the Lookupvalue function look at the Claim Ref # and then report what the associated Yes / No response would be from the Allowed Amount Match column. This does not work because of the 3 arguments that Lookupvalue is looking for. What am I missing?