Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have table A and B, and I want to create table A&B. Either the solution is not as straightforward as I would think or I have a blackout.
Table A | ||
ID | Value | Source |
1 | 7 | SQL |
2 | 8 | SQL |
3 | SQL | |
Table B | ||
ID | Value | |
3 | 5 | |
Table A & B | ||
ID | Value | Source |
1 | 7 | SQL |
2 | 8 | SQL |
3 | 5 | SQL |
I'm kind of stuck here. My own solution is quite complicated and involves creating 2 fields and concatenating them
A:
LOAD ID,
Limit
FROM
(qvd);
Join A:
LOAD * INLINE [
ID, Limit Temp
2, 55
];
Temp:
Load *,
Limit & [Limit Temp] AS Newfield
Resident A;
Drop Table A;
Is there a better (more elegant) way to solve this?
Appriciate any help
KR Nigel
If it's exactly like that, use ApplyMap():
TableB:
MAPPING LOAD
* INLINE [
ID, Limit
2, 55
];
TableA&B:
LOAD
ID,
ApplyMap('TableB', ID, Value) AS Value, // if ID does not exist in TableB it will store the value existing in TableA
'SQL' AS Source
FROM ... // source for table A
The column "Source" was just there to show that a simple concatenate won't fix it. But the field can have other values than "SQL" only.
Fine. It's more important here if the use of ApplyMap() is enough, and this depends on whether Table B has 2 and only 2 fields. If it has more, it should be a JOIN or several nested ApplyMap()s.
And no, the CONCATENATE will not work because you don't want to add more lines, bot to modify those which already exist.