Assign a secondary date to a main date

amaaiia — Mon, 19 Sep 2022 06:56:36 GMT

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!

Re: Assign a secondary date to a main date

rubenmarin — Mon, 19 Sep 2022 11:41:28 GMT

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

topic Assign a secondary date to a main date in App Development

Assign a secondary date to a main date

Re: Assign a secondary date to a main date