BusinessObjects Board

How to Merge and Simulate a SQL Join Based on Something Other Than Equality?

building reports webintelligence
#1

I am familiar with how to merge dimensions where I have two queries and a common object where I want to match on when their values are equal. But what about if I want to connect on something other than equality (<, between, etc.)?

Here is what my sample data looks like (top two tables) and the result want (the bottom table)…

image

In SQL Server this is easy…

SELECT
x.ID
, x.CustomerID
, x.SomeDate
, COALESCE (y.SomeValue, 0) AS [SomeValue]
FROM #Table1 x
LEFT JOIN #Table2 y ON x.CustomerID = y.CustomerID
AND x.SomeDate BETWEEN y.StartDate AND y.EndDate;

Here is a dbfiddle demo with sample data.

If you want to you can pull the SQL for each table from the demo and put them in free-hand SQL queries to simulate this in Web Intelligence.

In Web Intelligence I am merging on CustomerID. I created detail variables for StartDate, EndDate, and SomeValue from my second query with my merged CustomerID as the associated dimension. Then created a variable name Var SomeValue Reference with the following formula (source data in top two tables, result in the bottom table)…

=[Var SomeValue] Where([SomeDate] Between([Var StartDate]; [Var EndDate]))

I am really close. After checking either “Avoid duplicate row aggregation” or “Show rows with empty dimension values” to get rid of #MULTIVALUE everywhere I get this…

image

However, ID 4 should also have a Var SomeValue Reference value of 350 since its SomeDate value is between 2022-06-21 and 2022-06-29.

What am I doing wrong? Is this possible?

Thanks,

Noel

#2

did you try using “>=” and “<=” instead of “between” ?
did you try converting your date into string or integer before comparing ?

#3

I modified my formula to this with no change…

=[Var SomeValue] Where([SomeDate] >=[Var StartDate] And [SomeDate] <= [Var EndDate])

To your second point, ID 3 is finding a value from the lookup table (query) so why not ID 4?

Update: I did convert all my dates to strings with FormatDate() and I am still getting the same result; no reference value for ID 4.

#4

I think I came up with a narrow scope answer.

Noel