BI Launchpad Lookup columns with two different universes

[ithomsen]

Hello,

I am trying to create a lookup in BI Launchpad from one query to another (both queries are different universes) to create one consolidated column. The first column from Universe A contains values that appear in two different columns from Universe B. I have only been able to merge Universe A Material X/Y with either Material X or Material Y from Universe B, but not both at the same time. Example is below - All data points are dimensions

Universe A Universe B Universe B

Material X/Y Material X Material Y

1 1 asap-bi.jpg

2 2 b

3 3 c

d 4 d

e 5 e

f 6 f

I want a single resulting column that returns the match from Universe A Material X/Y to Universe B Material X. Basically, If Material X/Y exists in Material X, keep it the same. If Material X/Y exists in Material Y, return Material X - I need Universe A for financial purposes, but need Universe B for Material information. I will use this resulting column to pull material information into the financials

Universe A Universe Merged

Material X (from Universe B) Flag

1 Y

2 N

3 Y

4 N

5 Y

6 Y

Thank you!

[Joe_Peters]

You can't join from one columns to two other columns as you have shown. What I think you need is three queries - the second and third queries being identical, but with different merges:

1: [Universe A].[Material X/Y] = [Query 2].[Material X]

2: [Universe A].[Material X/Y] = [Query 3].[Material Y]