Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
I created the Datekey as mentioned. Then did a outerjoin between 2 files. But the amounts are completely wrong..