Does anyone know how to solve the below problem! ( it is quite urgent)
I have 2 tables: when the first table has value, the output should be directly from the 1st table otherwise the output should be calculated by taking the second table.
The formula is
'IF(ISBLANK(Table1 column value),((SUM(sumof previous 5 columns from table 1)/sumof previous 5 columns from table 2)*(current column from Table 2)),Table1 column value)
Please find the attached excel for details.
Thanks in advance,
*Column names keep changing, but would remain same across both tables.
ColumReference.xlsx (16.4 KB)
one way you could do it would be via the Column Expressions node.
You could simply merge both tables into one single table and check if the content of a column is present via isMissing(val). Concerning the changing column names, you could create a separate column, containing the name of the column that should be accessed as the function column(“col_name”) can be nested, e.g.
Thus, at first the content of the column “my_cols” is being accessed and directly feeded into the outer column function, accessing the column with the provided name in “my_cols”. You can also do that with the previous 5 columns. Alternatively, you could store the index of the column into “my_cols” providing an easier possibility to access the previous columns, e.g.
my_col = column(“my_cols”)
sum = column(my_col - 1) + column(my_col - 2) + …
Hope that helps.