Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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