Webi merging Variable and Dimension

[system]

Hi BOB Community,

I’am new here and also a newbie in Webi.

I need to merge a variable with a dimension. After some research, i found, it is not possible. may be you have an idee how can resolve my problem:

I have 2 queries :

Query1:
Person, Parent, HouseId

A A 1
B A 2
C B Null

Query2:
HouseId, Number, Street

1 11 s1
2 22 s2

I want to build one report (HouseId, Number and Street for a Person, if the person has no House get the one of the Parent):

Person, Parent, HouseId Number, Street
A A 1 11 s1
B A 2 22 s2
C B 2 (HouseId from parent B) 22 s2

I merged the diemsnions HouseId from the queries but still only have data if the HouseId is not null.

So I created a new Variable

HouseId_Merged =  If IsNull([HouseId]) then (HouseId In [Parent]) else [HouseId] 

and it works for HouseId.

But i can’t get the Number and Street for the Person C.

Thank you for any suggestion

---

TomiLee (BOB member since 2015-07-01)

[system]

Try to Add both Number and Street as Detail objects for HouseId receiving from Query2.

Thanks,
Vamsi.

---

Vamsi99 (BOB member since 2010-11-26)

[system]

Hi Vamsi,

Thanks for you replay. I already did it with a new variable as details but it works only if the HouseId is not null for a Person
I get this

Person, Parent, HouseId_Merged, Number(Details), Street(Details)
A A 1 11 s1
B A 2 22 s2
C B 2 Null Null

Do you have any other idea

Thanks

---

TomiLee (BOB member since 2015-07-01)

[system]

Use Merge with IsNull()

Hope this will help…

---

PathFinder (BOB member since 2012-01-05)