Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Apply formula iteratively

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_namestart_dateend_date
AAA01/12/202003/12/2020
BBB05/12/202007/12/2020

 

data_table

iddate
102/12/2020
2

04/12/2020

305/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!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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")

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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")