Horrible subject but I’ve used all of my brain power today attempting to solve what I thought would be easy. I have two tables both with fields called account number. Each table has its own query. I’ve merged the queries on the account number field.
The first table has around 2,000 account numbers while the second table has about 500 account numbers. There are roughly 300 account numbers shared between the two tables. In my first column I would like to display all of the account numbers from the first table. In my second column I would like it to display the account number from table two if it matches the account number from table one. Otherwise I could like the row on second column to be blank.
Column 1 Column 2
12345 12345 <--- Same account number in both tables
12346 <--- Account number not found in table 2
12347 12347 <--- Same account number in both tables
I didn’t have any luck with that article. I think it was due to merging on Account Number and the example was a little different. Maybe a different approach would work. I really just need the report to display if Account Number in Query 2 is also present in Account Number from Query 1.
I tried an in list with =If[Account Number Query1] InList ([Account Number Query2]) Then “Yes” Else “No” but that didn’t work. It was missing a list element. I should mention each query is a different universe. The only like field is Account Number.
Account Number Query 1 Account Number Query 2
12345 Yes <--- Same account number in both tables
12346 No <--- Account number not found in Query 2
12347 Yes <--- Same account number in both tables
Did you merge both queries on Account Number ?
You wanto to show the Account that belongs to both query, right ?
If so, drop the merged Account Number and the individuals Account Number, it will force to show the intersection.
Hi Mak, thanks for providing the link for the Document I wrote. I don´t think it´s clear enough (very far from it …) , won´t you like to help me clarify it ?
Best Regards,
Rogerio
Thank you both for the replies. I want Column 1 to show all of the Account Numbers from Query 1. I want Column 2 to display if the Account Number from Query 2 matches Account Number from Query 1. I don’t care how that’s demonstrated in column 2. It could say “Account Number Not Found in Query 2” Or “No” or be blank.
If so, bring in Query 1 Account Num and any measure object ([Meas1]), in Query 2 Account Num and any measure variable ([Meas2]).
Merge on Account Num, Drop Account N um from Query 1 on the report , create a measure variable , [test] = If(Not(IsNull([Meas 2]));“Match”;"Dont Match]) and drop it at the side og [Account Num] from Query 1.
I´m attaching the wid that generated it
Its a good document on the whole and I have linked people to it a few times .
As far as clarfiying, the bits I would look at are:-
Instead of val1 and val2, name the measures “dp 1 measure” and “dp 2 measure” Explain the business questions answered by the set operator Union, Intersection, Minus, with reference to the example you have used. Name the columns, in your example, with a meaningful / illustrative name rather than det or test1, Test 2
I tried Rogerio and mcnelson’s suggestions but in both cases as soon as I add the second column the account numbers from my first column decrease. It won’t display account numbers from query 1 that aren’t in query 2. Maybe it’s the older version of BO we have. I have version 12.5 of Rich Client and overall I believe we’re on 3.1 or 4.0.
Using my mechanism you don’t even need to merge dimensions - all you need to do is create a new query that returns only the Account Number object, and in the query filters use “result from another query” and select the Account Number object from query 2 - this is enough to assemble a list of the account numbers in the SQL statement for this new query, which you can then parse for matches by following the rest of the instructions in the article.
I hadn’t extended the merged dimensions. That solved the problem. I’m all set now. Thank you all for the help. Mcnelson, the only issue with your suggestion is that there could potentially be thousands of account numbers which would be too many to use results from another query.
(BOB member since 2005-01-06)
(BOB member since 2005-01-06)
(BOB member since 2011-08-21)
(BOB member since 2011-08-21)
.
Instead of val1 and val2, name the measures “dp 1 measure” and “dp 2 measure”
.