LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: LauraLacey

Hello I'm trying to run some data where I need to find out if emloyee expenses transactions were made when the employee was on vacation. I have a file of transactions and dates and I have a file on holiday dates. I've totally drawn a blank as to what nodes I need to use to get the matches

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jmok

Hi Laura,

You could use the X-ref node (located in the Correlation section). You will join both data sets using the Date fields and it will give you three outputs.

- Left orphans (records from the left/top data set that could not be found in the right/bottom data set)
- Matches between the two data sets
- Right orphans (records from the right/bottom data set that could not be found in the left/top data set)

The matches in the middle will show you the expenses where the dates matches with the holiday dates. If you have any questions, please let me know. Thanks, Janice.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: awilliams1024

To expand on Janice's reply here is an example. As you want to identify an employee's expense items where the date incurred corresponds with one of their vacation dates, the key used to correlate the data will need to be a composite key that comprises:

- the employee's unique ID and the date the expense was incurred

compared with:

- the employee's unique ID and their vacation date


The test data in the Static Data nodes represents the data type that you would obtain if the data were imported from Excel files.
Typically, a Data Converter / Data Analyzer node is used to convert the data types of imported data. This is generally a good thing but you may not always want to change the data type of a field.
In this case, the employee ID number is prefixed with some string characters to ensure the Data Converter does not convert it to an integer.
The date fields have been converted to 'date' type and the X-Ref node shows how you could convert this to a string for use within the composite key.
The match output specification (on the Output tab) was modified to only output fields from the first input to prevent clashes with fields from the second input.



Regards,
Adrian
Attachments:
Audit_Employee_Expenses_on_Vacation_Dates.brg