Forum Discussion
filtering with unconnected table
Hi all
I need to make my calculated colomn filter on valid_from and valid_to in a unrelated tabel. My plan was to add datesbetween after value_id filter, to retrive correct value to correct dateperiod. but this doesen´t work
Any ides?
tabel1 this is my goal.
| some_stuff | date | some_value | calculated colomn (code below) |
| a | 20201001 | 10 | 100 |
| b | 20201001 | 10 | 150 |
| b | 20201101 | 10 | 100 |
table2 the other table
| valid_from | valid_to | value_id | some_value |
| 20201001 | 20991231 | 1 | 10 |
| 20201001 | 20201031 | 2 | 15 |
| 20201101 | 20991231 | 2 | 10 |
BG
/Måns
Anonymous , Try a new column like this in table 1
maxx(filter(table2, ((Table1[some_stuff] ="a" && table2[value_id] = 1) || ( Table1[some_stuff] ="b" && table2[value_id] = 1 ))
&& Table1[date] >= Table2[valid_from] && Table1[date] <= Table2[valid_to] ), Table2[some_value])
3 Replies
Anonymous , Try a new column like this in table 1
maxx(filter(table2, ((Table1[some_stuff] ="a" && table2[value_id] = 1) || ( Table1[some_stuff] ="b" && table2[value_id] = 1 ))
&& Table1[date] >= Table2[valid_from] && Table1[date] <= Table2[valid_to] ), Table2[some_value])That did it : )
thanks a bunch amitchandak