Combining two tables with Where not exists()

[heathqm]

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

[MayilVahanan]

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:

[heathqm]

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;

[MayilVahanan]

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;