topic Re: Extract Table Names from a Query in App Development

Extract Table Names from a Query

sidhiq91 — Tue, 21 Jun 2022 03:31:17 GMT

Hello Qlik Experts,

I have requirement here, I have a column which has the complete SQL query Executed. But I need to extract only the table Names from that Query in the new Column. Queries can sometimes be quite complex too. Please find below for the sample data. Please help on the below request.

Input Data:

Query Statement

Select * from [dbo].[Customer]

select FirstName, LastName, b.city

from [dbo].[Customer] a inner join [dbo].[GeoLocation] b

on a.GeographyKey=b.GeographyKey

select distinct SupplierKey, Name, a.ProductSubcategoryKey, ProductSubcategoryName

from [dbo].[Product] a inner join [dbo].[Supplier] b on a.SupplierId=b.SupplierKey

left join [dbo].[ProductSubcategory] c on a.ProductSubcategoryKey=c.ProductSubcategoryKey

Expected Output:

Query Statement	Expected Result
Select * from [dbo].[Customer]	[dbo].[Customer]
select FirstName, LastName, b.city
from [dbo].[Customer] a inner join [dbo].[GeoLocation] b	[dbo].[Customer], [dbo].[GeoLocation]
on a.GeographyKey=b.GeographyKey
select distinct SupplierKey, Name, a.ProductSubcategoryKey, ProductSubcategoryName
from [dbo].[Product] a inner join [dbo].[Supplier] b on a.SupplierId=b.SupplierKey	[dbo].[Product], [dbo].[Supplier], [dbo].[ProductSubcategory]
left join [dbo].[ProductSubcategory] c on a.ProductSubcategoryKey=c.ProductSubcategoryKey

Re: Extract Table Names from a Query

sidhiq91 — Wed, 22 Jun 2022 02:29:03 GMT

Hello Qlik Experts,

Can someone please help me with the above request? This is a very important requirement in my Project.

Thanks in advance.

Re: Extract Table Names from a Query

vinieme12 — Wed, 22 Jun 2022 06:13:30 GMT

as below

Queries: LOAD querystring,SubStringCount(querystring,'[dbo]') as tableCount FROM [lib://AttachedFiles/test.xlsx] (ooxml, embedded labels, table is Sheet1); TableNames: Load * ,TextBetween(querystring,'[',']',iterno()*2) as tableName While iterno()<= tableCount ; LOAD * Resident Queries; Drop field tableCount; exit Script;

in table chart

Dimension

=querystring

Measure

=Concat(Distinct tableName,',')

Re: Extract Table Names from a Query

sidhiq91 — Wed, 22 Jun 2022 11:33:51 GMT

@vinieme12 Thank you so much. I have a question what if there were no [dbo] in the Query?

Re: Extract Table Names from a Query

vinieme12 — Thu, 23 Jun 2022 04:56:41 GMT

Assuming your strings are mixed some with [dbo] and some without [dbo], check the attached excel file

Load querystring ,if(index(froms,' '), subfield(froms,' ',1) , froms) as froms ,if(index(joins,' '), subfield(joins,' ',1) , joins) as joins ; LOAD querystring ,trim(if(index(querystring,'join'), TextBetween(querystring,'from','join') ,subfield(querystring,'from',2) )) as froms ,trim(subfield(querystring,'join',2)) as joins FROM [lib://AttachedFiles/test.xlsx] (ooxml, embedded labels, table is Sheet1); exit Script;

Output

Re: Extract Table Names from a Query

sidhiq91 — Sat, 25 Jun 2022 13:03:38 GMT

@vinieme12 Thank you so much for your effort to solve this for me. I did not get 100% result as the Query is sometimes too complex. But I would still consider your solution.