How to concatenate and then union these 2 files together wout nulls/blanks etc

[Hi2023]

I have concatenate the rows into 3 diff columns

Substring (aci_number, 0,10)

aci_number + acct_number 

aci_number + acct_number + amt_number 

but one file the aci  has blanks so I added a filter to filter out nulls (didnt work) 

the amt_number on both files format is different so I am not quite sure how to clean this part out as well

the results after the concatenate should only add actual #s not any blanks /nulls to those 3 columns because after this I am checking for those 3 types of duplicates but it is picking up the blank and making duplicates when they should not be duplicates

ex.

aci_number + acct_number    the aci_number is blank but its showing -12345 is a duplicate with -12345 which is NOT correct because it is not a duplicate of 2 categories only 1(acct_number)

how do I fix this?

thanks

dummy data attached

[ShankerV · Accepted solution]

Hi @Hi2023 

I have seen both the input files and unable to derive the logic explained to concat.

Could you please explain with an output expected.

Here are the input:

file 1

aci_number	acct_number	amt_total	name	address	type	status
	837027008	6700	jam	123 st	a	open
0444422414	837027008	6700	joe	123 st	b	closed
0391930532	31492.36	jell	123 st	c	open
0391930533	###########	31492.36	mell	123 st	d	closed
0220482109	80938817	100396.9	abc	123 st	e

file 2

control	ffer date	aci_number	acct_number	amt_total	loc	name
1	10/02/2020	000123456	0001268888	7500	miami	smith
2	10/02/2021	0001256789	000122222	12011.1	nyc	jane
3	10/02/2022	0001298765	000198765	5000	denver	doe
4	10/02/2023	000120045	0001299999	21586.26	miami	noa

Please share togic to combine both files and expected output.

Sharing expected output will help to understand the issue better and help to build solution.

Many thanks

Shanker V

[Hi2023]

I am going to union them together so I dont need a unique identifier for that but it would the ACI # 

this is a 2 part question:

first how to clean or make sure it will concatenate correctly (they are VStrings)

then when I am finding duplicates within both files---it is picking up the blanks -nulls-0 etc which I think is the real problem 

I updated the files to possible help.

exmaple of expected output

0444422413-0001268888-7500
aci -acct number-amt

that is a duplicate

-0001268888-7500 (for finding duplicates within files after a union)
aci is blank so no # just a -  and if it finds another one with aci blank it thinks its a duplicate so I getting the wrong dup count

same thing below-

0444422412-126888-0
ACI-ACCT NUMBER-AMT

(when doing the second part after union) I am trying to find the duplicates against aci and acct #s and amts not 0 or blanks, what is best way to get rid of them or so it wont pick them up at all for the second part?

if it finds 2 of the 

0444422412-126888-0

it states duplicates but its not because the zero shouldnt count it has to be matching in all those 3 categories ACI, ACCT_number, and AMT

hope this makes sense

[Hi2023]

I was able to figure it out -thanks!