One Field filled with two tables

[radokotbk]

Hi,

I have to fill the field "Client" of a table like this:

Key	Sub Key	Client
123240931	2054521
123240931	1505012
123240931	2506543
724561238	2602546
724561238	1251202
876546211	1236549

Where 1 Key can have n Sub Keys. My source tables are something like this:

Key	Client
123240931	Client A
724561238	Client C

Sub Key	Client
1236549	Client D

So my result is:

Key	Sub Key	Client
123240931	2054521	Client A
123240931	1505012	Client A
123240931	2506543	Client A
724561238	2602546	Client C
724561238	1251202	Client C
876546211	1236549	Client D

So for each row i have to check if the Key or the Sub Key have a Client associated (i know beforehand that there are no cases where both conditions are true at the same time)

Thank you and sorry for my english.

[albertovarela]

As there are no cases where both conditions are true at the same time. You could left join the first table with Keys, then the one with Sub Keys and then keep the field with a value. See attached qvw

I hope it helps

[maxgro]

you can use a nested applymap

MapKey:

Mapping LOAD * inline [

Key, Client

123240931, Client A

724561238, Client C

];

MapSubKey:

Mapping LOAD * inline [

Sub Key, Client

1236549, Client D

];

Source:

load * inline [

Key, Sub Key

123240931, 2054521

123240931, 1505012

123240931, 2506543

724561238, 2602546

724561238, 1251202

876546211, 1236549

];

Final:

LOAD

  Key,

  [Sub Key],

  ApplyMap('MapKey', Key, ApplyMap('MapSubKey', [Sub Key])) as Client

Resident Source;

DROP Table Source;

Don't join - use Applymap instead