Alteryx Designer Desktop Discussions

Agarwal_Yash91 · ‎08-17-2021

Hi,

we are trying to get the list of various tables used in SQL queries as input for alteryx workflows.

we have derived queries using xml parse and now trying to get list of tables present in input query.

List of Schema names are available in separate sheet.

Example:

Input 1:

Source System	Schema Name
PROD	Schema_1
PROD	Schema_2
PROD	Schema_3

Input 2:

Tool ID	Table query
1	select *A. name ,B.id from schema_1.table1 A join schema_1.table2 B on a.no=b.no
2	select *A. name ,B.id from schema_2.table1 A1 join schema_2.table2 B1 on a.no=b.no
3	select *A. name ,B.id from schema_3.table1 A2 join schema_1.table2 B2 on a.no=b.no
4	select *A. name ,B.id from schema_1.table3 A3 join schema_1.table2 B on a.no=b.no

Expected Output:

Tool	Tables in Use
1	schema_1.table1,schema_1.table2
2	schema_2.table1 , schema_2.table2
3	schema_3.table1 ,schema_1.table2
4	schema_1.table3 ,schema_1.table2

Any idea?

Thanks

mceleavey · ‎08-17-2021

Hi @Agarwal_Yash91 ,

I've done this by the cunning use of Regex...

As I can only assume your text string are representative of the real data, I have built the regex to take everything from the word "from" to the first instance of a space and an uppercase letter:

(?<=from)(.*)(?=\s\u)

This gives the following result:

Workflow attached.

Hope this helps,

M.

atcodedog05 · ‎08-17-2021

Hi @Agarwal_Yash91

Here is my take. I went a step ahead in Regex 😜

Workflow:

Hope this helps : )

Agarwal_Yash91 · ‎08-17-2021

Hi @atcodedog05 is not necessary that Schema name will start or will contain word Schema. schema details are available in different input sheet, i hope it makes some sense.