Max date for record in table before each date in second table

[aolpin]

Hello,

I am trying to get the last date of a record before every in a range . 

Transaction Table:

Customer ID	Transaction date
1	1/10/2022
1	4/10/2022
2	3/10/2022
3	5/10/2022
3	8/10/2022
3	10/10/2022

I generate a date table in the data load editor by using the first and last date in the transaction table date range and auto generating the dates in between using

MinMaxDate:
Load Min([Transaction Date]) as MinDate,
Max([Transaction Date]) as MaxDate
resident [Transaction Table];

Let vMinDate = Peek('MinDate',-1,'MinMaxDate')-1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate');
Drop Table MinMaxDate;

MasterCalendar:
Load
[Master Date];
Load
Date(recno()+$(vMinDate)) as [Master Date] Autogenerate vMaxDate-vMinDate;

This creates a table as follows:

All Date Table

Date

1/10/2022

2/10/2022

3/10/2022

4/10/2022

5/10/2022

6/10/2022

7/10/2022

8/10/2022

9/10/2022

10/10/2022

The results I am looking for are as follows

Client ID	Date	Last Transaction Date
1	1/10/2022	1/10/2022
1	2/10/2022	1/10/2022
1	3/10/2022	1/10/2022
1	4/10/2022	4/10/2022
1	5/10/2022	4/10/2022
1	6/10/2022	4/10/2022
1	7/10/2022	4/10/2022
1	8/10/2022	4/10/2022
1	9/10/2022	4/10/2022
1	10/10/2022	4/10/2022
2	1/10/2022	-
2	2/10/2022	-
2	3/10/2022	3/10/2022
2	4/10/2022	3/10/2022
2	5/10/2022	3/10/2022
2	6/10/2022	3/10/2022
2	7/10/2022	3/10/2022
2	8/10/2022	3/10/2022
2	9/10/2022	3/10/2022
2	10/10/2022	3/10/2022
3	1/10/2022	-
3	2/10/2022	-
3	3/10/2022	-
3	4/10/2022	-
3	5/10/2022	5/10/2022
3	6/10/2022	5/10/2022
3	7/10/2022	5/10/2022
3	8/10/2022	8/10/2022
3	9/10/2022	8/10/2022
3	10/10/2022	10/10/2022

I am able to do this in a Table and Chart as calculations but it is very slow and times out when there is no filter is applied to customer ID. I would like to do this as part of the data load to improve times, but have not figured out an efficient way of doing this.

[rubenmarin · Accepted solution]

Hi, you can do an 

Outer Join (MasterCalendar)
LOAD FieldValue('Customer ID', RecNo()) as [Customer ID] AutoGenerate FieldValueCount('Customer ID');

So you have a cartesian table with all customers and Ids.

Then you can add to the transaction table the ones that don't already exists, to do this you can add a field in transaction table to register the ones that exists

LOAD ...
  [Customer ID] &'_'& [Transaction date] as CustomerDateLoaded
...

And add from the cartesian table the ones that fills the gaps:

Concatenate ([Transaction Table])
LOAD [Customer Id], Date from MasterCalendar
where not exists('CutomerDateLoaded', [Customer ID] &'_'& [Transaction date])

With all the gaps filled you can do a sorted load and use Peek() to retrieve the prrevious date when CustomerDateLoaded is null

[aolpin]

It took some doing to get the peek statement right, but this worked, thank you