Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. I have two tables that I want to combine into one table. I want all the records from table A in the combined table and all the records from table B that are not in table a to be in the combined table. I am trying to use a where not exists clause but its not working.
Table A:
Load
Year & ' - ' & Account & ' - ' & BL & ' - ' & Title AS [Key1]
from [source1];
Table B:
Load
Year & ' - ' & Account & ' - ' & BL2 & ' - ' & Title2 AS [Key2]
resident [source2];
Then I want to add table B onto table A where the combination of Account & ' - ' & BL2 do not exist in table A.
Example:
Table A
123-YYYY-Happy
123-ZZZZ-Fields
Table B
123-YYYY-Happyssssss
123-PPPP-Yellow
Combined Table:
123-YYYY-Happy
123-ZZZZ-Fields
123-PPPP-Yellow
HI
Try like below
TableA:
Load Key1 as Value, Account&'|'&BL as Key Inline
[
Account ,BL, Key1
123,YYYY,123-YYYY-Happy
123,ZZZZ,123-ZZZZ-Fields
];
TableB:
Concatenate
Load Key2 as Value Inline
[
Account ,BL2, Key2
123,YYYY,123-YYYY-Happyssssss
123,PPPP,123-PPPP-Yellow
]Where not Exists(Key, Account&'|'&BL2);
DROP Field Key;
EXIT SCRIPT;
O/P:
I am having a hard time making sense of what you wrote. The data is coming from a file not inline load. I am also not sure which field you're dropping with the DROP Field Key. Would it be like this:
Table A:
Load
Year & ' - ' & Account & ' - ' & BL & ' - ' & Title AS [Key1]
Account & ' - ' & BL as Key
from [source1];
Table B:
concatenate
Load
Year & ' - ' & Account & ' - ' & BL2 & ' - ' & Title2 AS [Key2]
resident [source2];
Where not Exists(Key, Account&'|'&BL2);
DROP Field Key;
EXIT SCRIPT;
Hi
Yes, your code is correct.. Except in one place
Table A:
Load
Year & ' - ' & Account & ' - ' & BL & ' - ' & Title AS [Key1]
Account & ' - ' & BL as Key
from [source1];
Table B:
concatenate
Load
Year & ' - ' & Account & ' - ' & BL2 & ' - ' & Title2 AS [Key2]
resident [source2];
Where not Exists(Key, Account&' - '&BL2);
DROP Field Key; // dropping this field due to its not required in the front end.
EXIT SCRIPT;