topic Re: Search data load scripts in all apps for specific table names in App Development

Search data load scripts in all apps for specific table names

mikegrattan — Thu, 31 Mar 2022 18:08:23 GMT

I would like to know if there is a way to search for key words, such as a source table name, in all data load scripts across all apps. I'm trying to determine which apps use specific tables from our data warehouse in order to clean up the tables that are not being used by any apps.

Re: Search data load scripts in all apps for specific table names

abhijitnalekar — Thu, 31 Mar 2022 19:40:17 GMT

Hi @mikegrattan ,

You can load all your QVD in one application.

but while loading the QVD select xml from the type.

After loading the QVD.

you can select the table name from the field list

you can load all the QVD which have been generated from all the apps . and get the table list.

Re: Search data load scripts in all apps for specific table names

mikegrattan — Thu, 31 Mar 2022 20:11:00 GMT

Abhijit,

Thanks for the reply, but my requirement is to search all data load scripts for a table or tables with specific names and specific data sources; these are ODBC connections and not QVD files.

Example:

I would like to search data load scripts across all applications for the CCITEMMAST table and get a list of which applications are using that table in a data load.

Re: Search data load scripts in all apps for specific table names

abelmottac — Wed, 05 Oct 2022 21:52:05 GMT

Hi @mikegrattan

I'm with a similar need that you raise.

Have you got yourself any solution for your problem?

Thanks
Abel

Re: Search data load scripts in all apps for specific table names

mikegrattan — Wed, 05 Oct 2022 22:22:56 GMT

Hello @abelmottac . No, I have not received a solution to this question. If this capability is not currently in Qlik I am hoping that Qlik will see this post and consider it to be an enhancement request.

Re: Search data load scripts in all apps for specific table names

abelmottac — Thu, 06 Oct 2022 14:55:32 GMT

Thanks, Mike, for your answer.

I think the same, this would be an enhancement

Re: Search data load scripts in all apps for specific table names

curiousfellow — Wed, 02 Nov 2022 13:52:18 GMT

Searching for the same. So I vote for adding this feature

Re: Search data load scripts in all apps for specific table names

BeeGees — Fri, 09 Dec 2022 14:57:46 GMT

Working with Qlik You have to be creative 😉 As it lacks a lot of administering options (sadly).

First I've created txt file in some folder connection location (lets say lib://Scripts).

It contains those lines:

LET vAppId = DocumentName(); LET vAppName = DocumentTitle(); Trace APP_ID|$(vAppId)|APP_NAME|$(vAppName);

This is the script I use in ALL of my application in Qlik instalations (ETLs, CALCs, visialisation APPS, etc). So You have to inject it at the beginning of each APP:

$(Include=[lib://Scripts/AppNameTrace.txt]);

Then I've made special appliacation when I keep track of those things. It loads all the script load log files:

for each FoundFile in filelist( 'lib://LogsFolder' & '\Script\*.log' ) if $(vMinFileDate) < FileTime('$(FoundFile)') then trace $(FoundFile); FileList_TMP: Load distinct Text(left(Right('$(FoundFile)', len('$(FoundFile)') -index('$(FoundFile)','/',-1)),36)) as App_ID, if(upper(@1) like '*STORE*','STORE','READ') as QVD_ACCESS_TYPE, Replace(@1,'/','\') as QVD_FILES, FileTime('$(FoundFile)') as ReloadTime FROM '$(FoundFile)' (txt, utf8, no labels, delimiter is '\t', msq) where Upper(@1) like '*.QVD*'; AppNames_TMP: Load distinct Text(SubField(@1, '|', 2)) as App_ID, SubField(@1, '|', 4) as App_NAME FROM '$(FoundFile)' (txt, utf8, no labels, delimiter is '\t', msq) where Upper(@1) like '*APP_NAME*' and not Upper(@1) like '*TRACE*' and not Upper(@1) like '*UPPER*'; end if; next FoundFile;

Keep in mind that this is only for very recent scripts. If You want full history You have to also repeat above for each archive logs folder for each node in Your environment.

Last but not least some formatting for better App & QVDs names:

FILE_QVD_TABLE: NoConcatenate load App_ID, QVD_ACCESS_TYPE, replace(replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd'), 'qvd](qvd','qvd') as QVD_FILE, Date(floor(max(ReloadTime))) as LAST_RELOAD Resident FileList_TMP where upper(replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd')) like '*QVD' and not replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd') like '*''*' group by App_ID, QVD_ACCESS_TYPE, replace(replace(left(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)),len(Right(QVD_FILES,len(QVD_FILES)-Index(QVD_FILES,'\',-1)))-1), 'qvd] (qvd','qvd'), 'qvd](qvd','qvd'); join (FILE_QVD_TABLE) Load distinct App_ID, App_NAME RESIDENT AppNames_TMP WHERE not App_NAME like '*''*'; Drop Table FileList_TMP, AppNames_TMP;

I'll leave visualisations for Yourself.