Import a list of dates and URNs and run a selection to find the date in the system with the same URN and previous to the one in the list.

Import a list of dates and URNs and run a selection to find the date in the system with the same URN and previous to the one in the list.

A client asked us for a method of importing a list of URNS and Dates, into an existing FastStats system, and then to suggest a method to compare the imported date to other dates for the same URN in order to then provide the previous date attached to that URN.

To do this we carried out the process below (in this example within the Apteco Training system):

We created a list  of URNs and Dates in the format the client was using:

Person URN, Booking Date
96,09/02/2023
135,19/02/2023
134,07/02/2023
147,17/05/2022
334,13/04/2023
437,14/06/2023
622,20/06/2023
723,10/06/2023
725,18/08/2023
727,06/04/2022

We used the Import Data Wizard to import the dates onto the Person table.  Within the wizard changing the format of the data to match the rows in the system:


The variable was named as 'Imported Date'

Click the Expression button
Set the Resolve Table to be Bookings
Create an Expression like this:



This just flags a 1 if the Booking Date on that Booking record is older than the date imported at the Person table level. We used the Month() function to check that the Imported Date is not Unclassified because we only want to flag records that are present in the imported file. Any blank dates would have a month of 0.

Drag this Expression onto a new Selection
Enter a value of 1 as the selection criteria.

This selection is then selecting all the transactional (Booking) records which are more recent than the Imported Date.

We named this selection 'Older Transaction Flag' :


Now, click the Expression button to create a new Expression
Click the Aggregation button
Specify the Type as Recency
Set the Order records by to be  Booking Date
Leave the From as Latest to Earliest  as we want the most recent one
Set the Pick the option to be Booking Date
Drag and drop the filtered selection onto the Using these transactions drop area:


This Expression then looks like this:


This produces the required Last Date before the Imported Date.
Putting this on a Data Grid to confirm:


The New Expression 4 column shows the date returned by the on-the-fly aggregation. It should be the first populated date that occurs before the date we imported.


    • Related Articles

    • Counting specific days \ day types between 2 dates.

      A user asked it was possible to count the number of working (weekdays) between 2 dates. They had considered the function Datediff() within an expression but realised this simply counts the total number of days between 2 dates. Our suggestion was to ...

    • Issue with date ranges in selections reverting to "Earliest to Latest" on Q1 2024 release.

      Several users reported an issue that when creating selections within FastStats which included a date variable and contained a selection using a Date Range,. They found that after making changes to the other parts of the selection and subsequently ...

    • PS Unable to find the versioned file

      In Q3 23 we changed the default stance on versioned selections to error if it can not find the latest version available for the selection that is being used in the Campaign. To revert this back to use versioned files in the db, please set this in the ...

    • Expression Index Geo Nearest

      An issue arose when a selection was created from an Geo Nearest Expression using an index. The problem was that the place was not corresponding to the coordinates on the map Visualisation. E.g. When choosing Warwick in the list on the selection and ...

    • Admin User cannot see Users Explorer tab or Licences and Bundles screen in FastStats

      A user who believed they were set up as an admin user, reported logging into FastStats but not seeing either the Users tab or the Administration options. After checking the users settings were correct, we realised that in this case the issue was ...