Lookup based analysis

[devsonali]

Hello

Can anybody please guide me for the following

I need to look up a table A and based on matched results (on a key value) i need to create a virtual table


so a virtual table say "VIRTUALA" is primarily based on table 'A' but VIRTUALA table needs to contain only those values of its columnC1 that exists in Table 'B'

Any help is appreciated

[dsusersaj]

Hello

Can anybody please guide me for the following

I need to look up a table A and based on matched results (on a key value) i need to create a virtual table


so a virtual table say "VIRTUALA" is primarily based on table 'A' but VIRTUALA table needs to contain only those values of its columnC1 that exists in Table 'B'

Any help is appreciated

Are you creating your target table VIRTUALA on the go based on the lookup result?.

I guess not and it already exists.

So on that assumption I beleive your requirement is to join tables B and A on columnC1 which exists on both tables,and load the matched records to VIRTUALA.

Is that what you are looking for?

[devsonali]

Hello

Can anybody please guide me for the following

I need to look up a table A and based on matched results (on a key value) i need to create a virtual table


so a virtual table say "VIRTUALA" is primarily based on table 'A' but VIRTUALA table needs to contain only those values of its columnC1 that exists in Table 'B'

Any help is appreciated

Are you creating your target table VIRTUALA on the go based on the lookup result?.

I guess not and it already exists.

So on that assumption I beleive your requirement is to join tables B and A on columnC1 which exists on both tables,and load the matched records to VIRTUALA.

Is that what you are looking for?

Yeah , The virtual table already exists and the lookup is as good as an inner join . So that is pretty much the requirement - Any pointers how to handle this ?

[devsonali]

Hello

Can anybody please guide me for the following

I need to look up a table A and based on matched results (on a key value) i need to create a virtual table


so a virtual table say "VIRTUALA" is primarily based on table 'A' but VIRTUALA table needs to contain only those values of its columnC1 that exists in Table 'B'

Any help is appreciated

Are you creating your target table VIRTUALA on the go based on the lookup result?.

I guess not and it already exists.

So on that assumption I beleive your requirement is to join tables B and A on columnC1 which exists on both tables,and load the matched records to VIRTUALA.

Is that what you are looking for?

Yeah , The virtual table already exists and the lookup is as good as an inner join . So that is pretty much the requirement - Any pointers how to handle this ?

[dsusersaj]

Hello

Can anybody please guide me for the following

I need to look up a table A and based on matched results (on a key value) i need to create a virtual table


so a virtual table say "VIRTUALA" is primarily based on table 'A' but VIRTUALA table needs to contain only those values of its columnC1 that exists in Table 'B'

Any help is appreciated

Are you creating your target table VIRTUALA on the go based on the lookup result?.

I guess not and it already exists.

So on that assumption I beleive your requirement is to join tables B and A on columnC1 which exists on both tables,and load the matched records to VIRTUALA.

Is that what you are looking for?

Yeah , The virtual table already exists and the lookup is as good as an inner join . So that is pretty much the requirement - Any pointers how to handle this ?

Option 1: Use lookup stage if the tables A and B are not very big. (Should not contain millions of records).You can se 'Drop' on lookup failure if the records are to be dropped.Or 'reject' if you want to capture these records in a file.

Option 2: If the tables are big, then use a join stage with inner join on ColumnC1.

Also make sure you sort the data before joining on the key column.

[devsonali]

Are you creating your target table VIRTUALA on the go based on the lookup result?.

I guess not and it already exists.

So on that assumption I beleive your requirement is to join tables B and A on columnC1 which exists on both tables,and load the matched records to VIRTUALA.

Is that what you are looking for?

Yeah , The virtual table already exists and the lookup is as good as an inner join . So that is pretty much the requirement - Any pointers how to handle this ?

Option 1: Use lookup stage if the tables A and B are not very big. (Should not contain millions of records).You can se 'Drop' on lookup failure if the records are to be dropped.Or 'reject' if you want to capture these records in a file.

Option 2: If the tables are big, then use a join stage with inner join on ColumnC1.

Also make sure you sort the data before joining on the key column.

Are you talking in terms of datastage - I want to join this table and create a virtual table in IA and then run column analysis?

[devsonali]

Are you creating your target table VIRTUALA on the go based on the lookup result?.

I guess not and it already exists.

So on that assumption I beleive your requirement is to join tables B and A on columnC1 which exists on both tables,and load the matched records to VIRTUALA.

Is that what you are looking for?

Yeah , The virtual table already exists and the lookup is as good as an inner join . So that is pretty much the requirement - Any pointers how to handle this ?

Option 1: Use lookup stage if the tables A and B are not very big. (Should not contain millions of records).You can se 'Drop' on lookup failure if the records are to be dropped.Or 'reject' if you want to capture these records in a file.

Option 2: If the tables are big, then use a join stage with inner join on ColumnC1.

Also make sure you sort the data before joining on the key column.

Are you talking in terms of datastage - I want to join this table and create a virtual table in IA and then run column analysis?

[devsonali]

Are you creating your target table VIRTUALA on the go based on the lookup result?.

I guess not and it already exists.

So on that assumption I beleive your requirement is to join tables B and A on columnC1 which exists on both tables,and load the matched records to VIRTUALA.

Is that what you are looking for?

Yeah , The virtual table already exists and the lookup is as good as an inner join . So that is pretty much the requirement - Any pointers how to handle this ?

Option 1: Use lookup stage if the tables A and B are not very big. (Should not contain millions of records).You can se 'Drop' on lookup failure if the records are to be dropped.Or 'reject' if you want to capture these records in a file.

Option 2: If the tables are big, then use a join stage with inner join on ColumnC1.

Also make sure you sort the data before joining on the key column.

Are you talking in terms of datastage - I want to join this table and create a virtual table in IA and then run column analysis?

[dsusersaj]

Are you talking in terms of datastage - I want to join this table and create a virtual table in IA and then run column analysis?[/quote]

Yes I am talking in terms of datastage.
Btw,what is IA?.

[dsusersaj]

Are you talking in terms of datastage - I want to join this table and create a virtual table in IA and then run column analysis?

Yes I am talking in terms of datastage.
Btw,what is IA?.[/quote]

Oh got you.Information Analyser. Sorry have not used it.

[devsonali]

Are you talking in terms of datastage - I want to join this table and create a virtual table in IA and then run column analysis?

Yes I am talking in terms of datastage.
Btw,what is IA?.[/quote]


Sorry - I am talking in Information Analyzer
Am I in the wrong Forum ?

[devsonali]

Are you talking in terms of datastage - I want to join this table and create a virtual table in IA and then run column analysis?

Yes I am talking in terms of datastage.
Btw,what is IA?.[/quote]


Sorry - I am talking in Information Analyzer
Am I in the wrong Forum ?

[dsusersaj]

No you are in the correct place. I thought this is datastage forum.
Good luck!

[ray.wurlod]

Create a virtual table based on the join, and then analyze the virtual table.