How to merge and replace field valuse in loadscript?

[Mr_Pearl]

Hi,

We got table A which has some missing field values which I want to repalce with content of Table B in our load script. I browsed through lot of options like concatenation, replace, join etc., but couldnt figure out any option that would do what I want. Can anyone please share your knowledge,

Table A:

ID	Street	Area	Postcode	Country	EntryDate
1	2	Thirlstane	7307	Germany	14/01/2021
2	54	Amiens	281568	Finland	4/02/2021
3
4	65	Albacore	158	Dubai	5/02/2021
5			Unknown
6	NA	NA	NA	-	NA

Table B:

ID	Street	Area	Postcode	Country	EntryDate
3	43	stillwater	56125	USA	5/01/2022
5	20	sinclair	5558	Australia	10/02/2022
6	7	Benny	985123	India	1/05/2022

Output - Table A:

ID	Street	Area	Postcode	Country	EntryDate
1	2	Thirlstane	7307	Germany	14/01/2021
2	54	Amiens	281568	Finland	4/02/2021
3	43	stillwater	56125	USA	5/01/2022
4	65	Albacore	158	Dubai	5/02/2021
5	20	sinclair	5558	Australia	10/02/2022
6	7	Benny	985123	India	1/05/2022

Bascially I want the null, empty, 'NA', 'Unknown' values to be replaced with values in Table B. I spend hours on this with no solution.

[Vegar · Accepted solution]

What about something like this. (Adjust the first where clause to better fit your needs)

Data: Load * From A Where EntryDate>0;

concatenate (Data) LOAD * From B where not exists(ID);

[Mr_Pearl]

Combination of Applymap and concatenate did the trick. Thank you @Vegar