Dates Field - Amount Difference

[raadwiptec]

I have 2 Files.. File 1 and File 2 both has date fields and mount fields and few other not common dimensions. only dates are common but these dates are in different formats..

Only the important fields as below.

File 1  -->  TransactionDate(YYYYMMDD), TransactionAmount

File 2 -->  PaymentDate(DD/MM/YYYY). PaymentAmount

What i require is when the dates are matching 

for ex: 20200101 and 01/01/2020 -- The Difference between TransactionAmount - PaymentAmount

I tried with an outer join and created a Final table using Resident, But the amounts are getting very higher for one or other reason.. which iam not sure...

[Zhandos_Shotan]

Hi!

Try to use Date# (FieldName, 'INPUT date format') function. Function converts dates using second parameters as input date format representation.

For your case:

Date#(TransactionDate, 'YYYYMMDD') as DateKey

and

Date#(PaymentDate, 'DD/MM/YYYY') as DateKey

[raadwiptec]

I created the Datekey as mentioned. Then did a outerjoin between 2 files. But the amounts are completely wrong..