topic Re: join two tables using a dynamic key in Water Cooler

join two tables using a dynamic key

Alex — Tue, 20 Nov 2018 15:42:19 GMT

Hi all,

I would like to join two tables (A and B) with the following structure:

A:
X, Y, Z

B:
X, Y, Z, P

The point is that X, Y or Z value is '*' in table B what means "all possible values".
In case X would be '*' I would like to join to A using only Y and Z.

How can I do that? I know in SQL, for instance, you can use functions like isnull(X,'*') so you're kind of ignoring some columns once joining.

Thanks in advance.

Re: join two tables using a dynamic key

vishsaggi — Tue, 20 Nov 2018 15:59:27 GMT

Can you share some sample data with your expected output please.? Not clear what exactly you are trying to do. You can use ISNULL() in QV too.

Re: join two tables using a dynamic key

Alex — Tue, 20 Nov 2018 16:16:54 GMT

Example:
(Table A):
X, Y, Z
M, UK, L

(Table B):
X, Y, Z,W
M,*,*,1
M,N,*,2

I would like to do an applymap on table A using as a Key "X & Y & Z" to get W column from B.
However, table B contains '*' sometimes which means "all values". In this case, I would like to ignore the columns where I have '*' and only use as a Key the rest.

In the example posted, only X would be my key for the first row.
X&Y would be the key for the second row, etc.

Re: join two tables using a dynamic key

vishsaggi — Wed, 21 Nov 2018 03:21:02 GMT

What is your expected output?

Re: join two tables using a dynamic key

marcus_sommer — Wed, 21 Nov 2018 09:46:12 GMT

One way might be to use multiple joinings each one with the appropriate fields and where-statements. But before I would check: Mapping-as-an-Alternative-to-Joining

- Marcus