How to reconcile two different data sets using reference data

[nayakdj2020]

Hi all,

Hope everyone is well and keeping safe. 

I am trying to compare two data sets using a reference sheet. I am trying to create a workflow that takes the account # and account names from the reference sheet and find account# from data1 & account names from data2. The second part is to reconcile the amounts between data1 & data2 and show us the line items which are not matching between data1 and 2 Note: Dataset 2 has the amount broken into two or more line items and data1 has the consolidated amounts. 

I will really appreciate any help here, thank you in advance.

Ref data: 

Accounts#	Account names	Account code
1230012	Expense	EXP-TT
9817063	Sales	SAL-TT
1283263	Ads	ADS-TT
1987063	Other	OTR-TT
8881791	Travel	TRL-TT
4531628	Pres and prod	PRE-TT
7613960	Telecom	Tel-TT

Dataset 1:

Business code	Accounts#	Currency	Amount
1000	1230012	EUR	1100
2000	9817063	USD	2500
3000	1283263	SEK	100
4000	1987063	USD	3200
5000	8881791	SAR	45
6000	4531628	EUR	340
7000	7613960	USD	110

Dataset 2:

Business code	Currency	Amount	Account names
1000	EUR	500	Expense
2000	USD	2200	Sales
3000	SEK	100	Ads
4000	SAR	200	Other
5000	SAR	45	Travel
6000	EUR	340	Pres and prod
7000	USD	90	Telecom
1000	EUR	500	Expense
2000	USD	600	Sales
4000	SAR	3000	Other
7000	USD	20	Telecom

Result: 

Business code	Currency	Accounts	break amount with file name
1000	EUR	1230012	100(more in dataset1)
2000	USD	9817063	300(more in dateset2)

[JagdeeshN]

Hi @nayakdj2020 ,

Please find attached a sample workflow which does the ETL you have referred to.

Do let me know if this helps.

Best,

Jagdeesh

[nayakdj2020]

Thank you @jagdeeshn for your help