I am want to query a big table (T1) based on values in a table T2 .
The condition is - filter all the rows in T1 in which value of Col A is included in the list of values in Col B of T2. Col A is a Text Column and Col B is a Text column with a list. Col B can be an array also. This works fine when Col A has a single value.
What I need is - filter all rows in T1 in which any value in Col A is included in the list of values in Col B of T2. Here Col A is a Text Column with a list of values. This doesn’t work.
Col A cannot be an array since array is not a basic column and computed columns cannot be used to query big tables.
I might be able to help, but I’m having difficulty visualising the situation. Would you mind adding a screen shot of each of the two tables?
Thanks - here are they
This is the table T2 and the Col can be “Search_ports” (Text) or “split_old_ports” (Array). These columns includes a value “VIDP”.
This is the Table T1 which needs to be queried. The condition is any of the values in Col “Base” (VIDP or VABB) should be included in the Col “Search_ports” or “split_old_ports”. When i am creating the query, Row 2 of T1 is returned but Row 1 is not. I need that both these rows should be returned in the query.
Just to Add, T1 is a big table.
Okay, that makes it clearer, thanks.
And yes, I see the challenge.
And I can’t immediately think of any workaround to this, sorry.
I think your only option would be to find a way to restructure the data in T1 so that the base column contains just a single value in each row.
Thanks got it. That would be difficult. For this functionality to work, the only workaround is add the same row multiple times with different values of base if the query uses AND logic or have more columns with different values of base while using a OR logic in query.
If I understood your case well, you need to create an array column based on your base column first and then, create a relation between this new column and your split_old_ports (array) column. In other words, you relation must use 2 arrays columns in order to find right values.
Something like this:
The same result will be obtained if the relation is made from your table T2.
I hope it helps @Abhishek_Sinha
You cannot create relations to computed columns in Big Tables
Trying to find a solution I discarded these 2 words from my mind in the way.
This is a great tool that anyone will miss using BT unfortunately. I don’t have a Business plan to test other ideas.
Thanks for the advice Darren.