on 08-13-2015 11:04 AM
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
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".
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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".
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
72 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.