Join two or more tables

owen3 · Post by **owen3** » Thu Jun 10, 2010 10:30 pm

I have a requirement to profile 3 columns of 3 different tables (which has a common key) and capture a result where either 3 of them are not equal or at least two of them are not equal.
I would really appreciate any thought or help.
Thanks in advance.

ETLJOB · Post by **ETLJOB** » Fri Jun 11, 2010 1:32 am

After your database read have a transformer and by using stage variables setup a flagvariable (Y or N)

Then check

1. If col1=col2=col3 the set flag as 'N'
2. If col1=col2 or col2=col3 or col3=col1 set flag as 'N'

Then, have a constraint to output the required records based on the flag value. Just an idea

owen3 · Post by **owen3** » Fri Jun 11, 2010 5:44 am

[quote="ETLJOB"]After your database read have a transformer and by using stage variables setup a flagvariable (Y or N)

Then check

1. If col1=col2=col3 the set flag as 'N'
2. If col1=col2 or col2=col3 or col3=col1 set flag as 'N'

Then, have a constraint to output the required records based on the flag value. Just an idea :idea:[/quote]

Thanks for the reply......but I am talking about from Information Analyzer prespective.......I have no idea where that Transformer coming from at IA (if you are talking about DS then we can write extract query with given condition).
I know there are two ways:
1. Create single table, Select/insert data with given condition, pull that table and profile
2. Create DS job based on given condition....load into table and profile on that table.
BUT what I am looking is ...is there any method within IA ...so that I can profile by joining three table.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 11, 2010 6:45 pm

Not directly within IA but, if you create an appropriate view in the source, then you can profile the view.

owen3 · Post by **owen3** » Fri Jun 11, 2010 8:51 pm

Thanks Ray for the reply.

Is there any possibilities by using virtual table or reference table or by using data rules within IA?

Members please feel free to put any suggestions or comments.

ray.wurlod · Post by **ray.wurlod** » Sat Jun 12, 2010 1:27 am

I don't know how to create a virtual table based on a join, but only because it's not something I've ever tried. I have profiled views in the past, with success.

Data rules won't really help, because you still have to get data from all three source tables. Data rules can tell you how many rows comply with each of your condition(s), but offer nothing in terms of filtering the rows.