Comparing database records

Guides • Data Integration & ETL • 25/02/2018

Overview

This article explains how to compare database records. Consider the following scenario:

The table below shows a list of service desk tickets for multiple customers. You wish to identify whether a customer opened a second new ticket within 7 days of closing a previous ticket.

To identify these customers, you would take the following steps:

Steps

1. Create a new query from table

The data should appear in the Query Editor, as follows:

Next, sort the data by the ‘Customer’, and then, by the ‘Open Date’ columns, as shown below:

2. Create Index columns

Create an Index Column, starting from “1”
Now, create another Index Column, this time starting from “0”, so you can match any given record to its previous record

The data should look as follows:

Rename the second index column to ‘Previous’

3. Join the table to itself

Now, join the table with itself, on the columns ‘Previous’ in the first table, to ‘Index’ in the second table, as shown below:

Now, remove the Index and Previous columns, and extract the ‘Customer’ name, and ‘Close’ date from the previous record, by clicking on Renamed Columns, as shown below:

After expanding the two columns, rename the Close Date Column, to Previous Close Date, so the Table looks like this:

4. Add a custom column

Now, write a custom formula that tells you whether the Previous Close Date record was opened within the last 7 days, if it was for the same customer.
To do this, add a custom column, and create the following if statement:

After clicking on OK, the Table should look like this:

Finally, remove the other columns, and then Close & Load the Query as a Table.

We have now arrived at our final solution!