Apply formula iteratively

[Anonymous]

Hi,

I have a table (info_table) with information I need to use to create a new column in a different table (data_table). 

info_table

promo_name	start_date	end_date
AAA	01/12/2020	03/12/2020
BBB	05/12/2020	07/12/2020

data_table

id	date
1	02/12/2020
2	04/12/2020
3	05/12/2020

Resulting column, in data_table:

promo_name

AAA

None

BBB

My formula:

promo_name = IF(data_table[date] >= RELATED(info_table[start_date]) && data_table[date] <= RELATED(info_table[end_date]), RELATED(info_table[promo_name]), "None")

The problem with this formula is it just maps the first row correctly.

Right now I'm getting around this by creating a formula and inserting the conditions I want manually, but I'd like it to update correctly according to the different promo_names in info_table.

Any help will be appreciated!

[amitchandak · Accepted solution]

@Anonymous , Create a new column in data_table

new column = coalesce(maxx(filter(info_table, info_table[start_date] <=data_table[date] && info_table[end_date] >=data_table[date]),[promo_name]),"None")