Comparing 2 objects from 2 different BEXes

[simonpeart]

I have 2 BEX queries and in each, there is an object that lists Account numbers.  The first BEX only lists Account numbers that have been active over the last 4 years, the second BEX lists all Account numbers.  My mission is to compare the 2 lists and come out with a list of accounts that we haven't dealt with over the last 4 years.  In the BEX that contains Active accounts, there is a measure that contains how much they ordered from us.

Now I thought it'd be simple and it'd be a case of merging the 2 Account Reference objects together and creating a variable that says "=if([Active Trading Partner]=[Trading Partner]) Then "Match" Else "No Match".  Problem is, it only seems to tell me that the only possible outcome is "Match" and will only allow me to see the Trading Partner's that match in both columns, it refuses to give me a list of accounts where it equals "No Match".  I've also tried creating a variable that uses isnull so =isnull([Ordered Quantity]) In([Trading Partner]) - this one gives me a 1 where there is no Ordered Quantity by the list of All Accounts and a 0 where there is an Ordered Quantity, however, if i filter on this and set me to only show Trading Partners that =1, it still shows me a list of all trading accounts rather than just the ones where it =1, if i set it to 0, it still makes no difference and shows me the full list.

So the 2 BEXes are

All Accounts

     - All Accounts Object

Active Accounts

     - Accounts

     - Ordered Quantity

[simonpeart · Accepted solution]

Ordered Quantity is a Measure object, the Trading Partner objects are both Dimensions.

First screen shows before I put the filter in the merged table, so the Merged Accounts table is a repeat of the first table so I get accounts with no Ordered Quantity and accounts with something in the Ordered Quantity column

This screen shot shows exactly what happens when I add the variable to the Merged Accounts table.  The top number in the accounts column is a count.  Bare in mind that there is no filter at all in terms of the variable and when I do add it as a filter, my only option is "Match".

[amitrathi239 · Accepted solution]

Hi,

Variable is Dimension or Measure?.If it is dimension variable then change to measure variable and see.

I have replicated the same and found whenever i have used dimension variable i am getting Match only.But when i changed to measure variable then I am getting Match & No Match both.

Amit

[simonpeart · Accepted solution]

That has worked fantastically, thank you.  I have no idea why it's worked and had to be a measure but it has.

[simonpeart]

Done it and still doesn't work.

Using the merged Account Numbers object, I add the new variable to it on the table expecting 2 columns, 1 with the merged account numbers and the second with the "Match" or "No Match", as soon as the variable is added to the account, it'll immediately jump to only showing "Matched" accounts and the only filter option is "Match".

[amitrathi239]

Hi,

Are you getting All account numbers in the table?

Check when you drag Ordered Quantity in the table then some  values are coming blank for account numbers?

Ordered Quantity is dimension or measure object?

Share some screen shot.

Also check this Link.It will be helpful.

Amit

[amitrathi239]

Hi,

Use this.

Var= If( Not ( IsNull([Ordered Quantity));"Match";"No Match")

Use the Merged Account number Object  in the table and use filter condition  Var equal to "No Match".

Amit