Assign a secondary date to a main date

[amaaiia]

Hello!
I have a table A:

date1	item1
2022-08-01	a
2022-07-31	b
2022-05-03	c

and a table B:

date2	item2
2022-07-31	1
2022-05-04	2
2022-01-02	3

I want to assign the item2 value to table A with the condition: date2 is before or equal date1 and it's the nearest one. That is:

date1 (date2)	item1	item2
2022-08-01 (2022-07-31)	a	1
2022-07-31 (2022-07-31)	b	1
2022-05-03 (2022-01-02)	c	3

 *Note that for date1 2022-05-03 I assign 2022-01-02 and not 2022-05-04 because date2 has to be before or equal.

What I need to do is to create a Table visualization where for each date1 I have to show the avg(item2) given that condition. It's not necessary to have the joined table en load script, but I can have the data in two tables and then create a measure after. I don't know which is the best way to solve this. Any ideas?

Thanks!

[rubenmarin · Accepted solution]

Hi, you can join all values to check wich one is the nearest to each date, something like:

tmpDatesJoined:
NoConcatenate LOAD date1 Resident TableA;
Outer Join LOAD date2 Resident TableB;

tmpNearestDate:
LOAD *
Where NearestDate=1;
LOAD 
	date1,
	date2,
	if(date2<=date1 and (Peek(NearestDate)<>1 or Peek(date1)<>date1)
	  ,1,0) as NearestDate
Resident tmpDatesJoined
Order By date1 asc, date2 desc;

DROP Table tmpDatesJoined;

Then you can use this dates as a mapping or in a join to get the data as you need