Forum Discussion
clteh9
Helper I
Helper Imatch the nearest record from another table
I have 2 tables and i would like to match the nearest record from another table by comparing table1's access_time and table2's permit_time and its category. thanks for the help
table1:
| item | category | access_time |
| 1 | A | 2022-11-23 08:12:00 |
| 1 | B | 2022-11-23 09:12:00 |
| 2 | C | 2022-11-22 13:12:00 |
| 3 | D | 2022-11-22 14:12:00 |
table2:
| item | category | permit_time | value |
| 1 | A | 2022-11-23 09:12:00 | 11 |
| 1 | A | 2022-11-23 08:30:00 | 12 |
| 1 | B | 2022-11-22 08:30:00 | 13 |
| 2 | C | 2022-11-22 13:12:00 | 21 |
| 3 | D | 2022-11-22 14:12:00 | 31 |
| 3 | D | 2022-11-22 14:30:00 | 32 |
expected result:
| item | category | access_time | permit time | value |
| 1 | A | 2022-11-23 08:12:00 | 2022-11-23 08:12:00 | 12 |
| 1 | B | 2022-11-23 09:12:00 | 2022-11-22 08:30:00 | 13 |
| 2 | C | 2022-11-22 13:12:00 | 2022-11-22 13:12:00 | 21 |
| 3 | D | 2022-11-22 14:12:00 | 2022-11-22 14:12:00 | 31 |
2 Replies
FreemanZSuper User
hi clteh9
Supposing your tables are related like:
try to add two columns one after another like:
permit_time = VAR _table=RELATEDTABLE(Table2) VAR _gapmin = MINX( _table, ABS(Table2[permit_time]-Table1[access_time]) ) VAR result = MAXX( FILTER( _table, ABS(Table2[permit_time]-Table1[access_time])=_gapmin ), Table2[permit_time] ) RETURN resultand
value = VAR _table=RELATEDTABLE(Table2) VAR result = MAXX( FILTER( _table, Table2[permit_time]=Table1[permit_time] ), Table2[value] ) RETURN resultit worked like:
- FreemanZ
hi clteh9
you may also try with measures, by plotting a table visual with all table1 columns and two measures like:
permit_time2 = VAR _access_time=MAX(Table1[access_time]) VAR _gapmin = MINX( Table2, ABS(Table2[permit_time]-_access_time) ) VAR result = MAXX( FILTER( Table2, ABS(Table2[permit_time]-_access_time)=_gapmin ), Table2[permit_time] ) RETURN resultand
value2 = VAR _access_time=MAX(Table1[access_time]) VAR _gapmin = MINX( Table2, ABS(Table2[permit_time]-_access_time) ) VAR result = MAXX( FILTER( Table2, ABS(Table2[permit_time]-_access_time)=_gapmin ), Table2[value] ) RETURN resultit worked like: