IN-DB Iterative Value Matching Across Multiple Columns

[tj]

Will try to explain my needs, hopefully somebody has related knowledge

I have a database table (T1) with below columns/values

ID	COL1	COL2	COL3
1	11	22	33
2	44	11	55
3	22	55	66
4	77	88	44

I wanted to compare below table (T2) with above table (T1) in database. Process as follows

• Check first value (11) in T2 against all 3 columns (COL1/2/3) in T1
• If any match, pull the whole row in T1
• Then add VALUE/MATCH columns from T2 to generate output rows
• Iterate until all 3 values are compared

VALUE	MATCH
11	A
33	B
44	C

The output table (T3) should be something like below

ID	COL1	COL2	COL3	VALUE	MATCH
1	11	22	33	11	A
2	44	11	55	11	A
1	11	22	33	33	B
2	44	11	55	44	C
4	77	88	44	44	C

As actual T1 table is quite big, so I'd like to utilize IN-DB tools as much as possible, before bring T3 table out as output results.

I'm thinking of using In-DB Macro but not sure how to accomplish it.

Appreciate any insights/help!

[AkimasaKajitani]

Hi @tj,

I tried to create the batch macro, but Macro Input In-DB tool can only be used in standard macros.

Please make the workflow without using macro. Let's give up this time.

[gabrielvilella]

Hi @tj, try using a batch macro with the Dynamic Input tool, updating the SQL query dynamically. The only caveat is that you will have to know how to write the SQL code for joining tables.

[tj]

Thank you both for your advice. 

Let me try batch with dynamic input to see how far I can push it.

[apathetichell]

can you use a pivot that's specific to your sql to isolate the rows - join those against your row id and then join the column with the matched values?

What type of SQL is your datasource?

[tj]

It's Snowflake so I assume standard ANSI SQL?

[apathetichell]

Ill try to play around with it later today and see where I get. But this should be possible in Snowflake.

https://docs.snowflake.com/en/sql-reference/constructs/unpivot.html has information on what I'd use...

[tj]

Appreciate your time in working on it, let me know what you find!

[apathetichell]

O.k. - Back to this - there is a fairly straight forward solution that I hadn't really thought of at the time.

1) Connect In-Db tool to pull 1 record from your data source - this will go to a datastream out. (select * from .... LIMIT 1)

2) Second branch from Connect In-DB to a dynamic In-DB tool. This will capture your query and connection.

3) You will decide what columns could contain your value - you can find all potential numeric columns via dynamic select looking for numeric columns (or string columns).

4) use a field info after the dynamic select to isolate your column names as name.

5) use a formula tool to change column name (now in the [name] field into something that would fit in your sql query (like [name] + = "'Best Column Ever'")

6) use a summarize tool to combine this column - making sure to use /n (new line) and "AND" in your seperator. Your prefix should be WHERE

7) union your SQL column code to your query/connection datastream. use manually layout columns to make sure your query and your concatenated columns are IN THE SAME COLUMN. use the bottom section of the union tool to choose which should go on top.

8) Use another summarize tool - take the max connection. concatenate your queries. again. use /n if you want to.

9) Use a formula tool - replace([concat_query],"LIMIT 1"," ") - you don't need to limit 1 anymore.

10) YOU ARE READY! DRAG A DYNAMIC CONNECT IN-DB ONTO YOUR CANVAS. Set up your connection and you query. let it run.

Troubleshoot as needed. Add browse tools after each summarize and formula to make sure that this looks like you'd expect it to.

NOTE: to get this in your ABC field. you would put this entire thing in a macro. you would then feed in the value you are matching with to step 5 to replace "Best Column Ever".