BusinessObjects Board

Get results from Concatenation fields different queries

building reports webintelligence
#1

Hi,

I have two queries where I Match 2 fields (Cancel & Change) these fields are a concatenation of

Query 1: RefNo, ID, Date, City, Cancel (Concatenated Field “RefNo;ID;Date)

Query 2; RefNo, ID, Date, City, Cancel (Concatenated Field “RefNo;ID;Date), more fields

Both queries bring distinct results from different tables:

Query 1: 50 results

Query 2: 25 results

Equal Concatenations 11 results

Table1

I created two detail variables from the Merged Field (Cancel & Change), the aim is to count the number of records “RefNo” where the concatenated fields are equal, so I build variable as

=COUNT(RefNo) WHERE(Cancel=Change;(Cancel;Change))

But it gives me the wrong results for some reason I get the total number of records from the query 1 “50”

Created a new variable “v_Compare”: =IF(Cancel=Change) Then 1 Else 0

Using this variable in the Filter do not change the results in the table,

Table2

however if I add the “v_Compare” variable in a new column the totals change to the correct volume (11)

Table3

What am I doing wrong here?

“Any help and All help is appreciated”

Using: SAP BusinessObjects BI Platform 4.3

#2

Sorry, you completely lost me with this one.

Should “Cancel” in Query 2 be “Charge”?

Are cancel and charge fields from the query or are they variables in the report?

Are you merging on the Cancel and Charge fields/variables? If not, what are you merging on?

What are the two detail variables you created from the merged field? You seem to indicate you created details of the merged fields themselves?

If you are in fact merging on Cancel/Change, the fields are going to match… it’s what you are merging on.

The Date, City, ID, Manager, etc fields that are in your data block and in your compare formulas… which query are they from??.. you indicated they exist in both queries.

Where is the merged dimension in your data block? When merging dimensions, the fields/merged dimensions you include in the block directly impact the data you see and results of calculations.

It might be easier to provide a sample dataset from query 1 and query 2 and a mock up of the results you expect.