Extract Table Names from a Query

[sidhiq91]

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

[sidhiq91]

Hello Qlik Experts,

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

Thanks in advance.

[vinieme12]

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,',')

[sidhiq91]

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

[vinieme12 · Accepted solution]

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

[sidhiq91]

@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.