Inserting columns from new table with filter

[S C]

Hi,

I have table A [main table]. I would like to add 2 columns from table B: Name and Value to table A. When i add the two columns from Table B to Table A, i want only those rows where the Name column contains 'X' and its corresponding values from Value column while maintaining the original number of rows from Table A.

I am able to add columns, use left join but when i try to filter it out 'X' , the entire set of data is filtered to match X, whereas i am looking to only apply the filter on Name/Value columsn.

Table A

ID Dept Dept NO

1 Abe 1211

2 Sino 4144

3 Mir 2134

 

TABLE B

ID NAME VALUE

1 A 2

1 B 2

2 X 1

2 B 5

3 A 8

 

New table

ID Dept Dept NO X

1 Abe 1211

2 Sino 4144 1

3 Mir 2134

[Fabian Duerr]

The answer to your question really depends on your requirement...

If this is something you only do once and there is no need for any automatic refresh then you could just take the filtered data to your clip board. (quick and dirty...)

A better solution might be to use a pivot on your table B to span the name column. But this will only work when you can guarentee that you always have only one ID-NAME combination. Because Pivoting means agggregating. In your case you have a value (numeric) and you can use MIN,MAX,... and so on. It will not matter if you have unique ID-NAME combination. For strings you would use UniqueConcatenate.

For any other solution you could write a data function that takes both tables as an input. Then filter table B before merging it with table A. This would give you the highest flexibility. For example you could create a doc property that will go to your filter: here 'X'. But you could quickly change it to 'A' and refresh the output.