topic Re: Extract the Inputs from the Log Files in a Directory in QlikView

Extract the Inputs from the Log Files in a Directory

microwin88x — Sat, 04 Oct 2014 18:59:35 GMT

Hello,

Is it possible to create a QVW to extract the inputs (files used - QVD, XLS) from the log files in a directory?

Thank you!

Re: Extract the Inputs from the Log Files in a Directory

MarcoWedel — Sat, 04 Oct 2014 20:12:24 GMT

please provide sample data and expected result.

thanks

regards

Marco

Re: Extract the Inputs from the Log Files in a Directory

microwin88x — Sat, 04 Oct 2014 20:27:18 GMT

Hello Marco,

Thanks for your reply.

The sample data (log) would be something like the following:

---

28/07/2014 6:14:44 PM: 0017 X_ReloadTime:

28/07/2014 6:14:44 PM: 0018 LOAD

28/07/2014 6:14:44 PM: 0019 NOW() AS StartTime,

28/07/2014 6:14:44 PM: 0020 OSUSER() AS ReloadUser

28/07/2014 6:14:44 PM: 0021 AUTOGENERATE(1)

28/07/2014 6:14:44 PM: 2 fields found: StartTime, ReloadUser, 1 lines fetched

28/07/2014 6:14:44 PM: 0027 REFERENCE_TABLE:

28/07/2014 6:14:44 PM: 0028 MAPPING

28/07/2014 6:14:44 PM: 0029 LOAD

28/07/2014 6:14:44 PM: 0030 FIELD_VALUE AS STATUS_FLG.

28/07/2014 6:14:44 PM: 0031 VALUE_NAME

28/07/2014 6:14:44 PM: 0032 FROM \\PROD\QVD\REFERENCE_TABLE.qvd (qvd)

28/07/2014 6:14:44 PM: 0033 WHERE FIELD_NAME='STATUS_FLG'

28/07/2014 6:14:44 PM: 2 fields found: SA_STATUS_FLG, VALUE_NAME, 8 lines fetched

28/07/2014 6:14:44 PM: 0036 ACCOUNTS:

28/07/2014 6:14:44 PM: 0037 MAPPING

28/07/2014 6:14:44 PM: 0038 LOAD

28/07/2014 6:14:44 PM: 0039 ACCOUNT_ID,

28/07/2014 6:14:44 PM: 0040 BILL_ID

28/07/2014 6:14:44 PM: 0041 FROM \\PROD\QVD\ACCOUNTS.qvd (qvd)

28/07/2014 6:14:44 PM: 2 fields found: ACCOUNT_ID, BILL_ID, 1,345,238 lines fetched

---

I need to read the log and then save in a field the values:

REFERENCE_TABLE.qvd
ACCOUNTS.qvd

Being those the inputs from the log. I would have to consider that there could be other log files in the directory where I'm reading the logs, so that I could bring the inputs for the corresponding log.

Do you know how could I do that?

Thank you.

Re: Re: Extract the Inputs from the Log Files in a Directory

MarcoWedel — Sat, 04 Oct 2014 21:54:32 GMT

Hi,

one solution might be:

LOAD Pick(WildMatch(LogLines,'*.qvd*','*.xls*'),

SubField(TextBetween(LogLines,'\','.qvd'),'\',-1)&'.qvd',

SubField(TextBetween(LogLines,'\','.xls'),'\',-1)&'.xls'

) as SourceFile

Inline [

LogLines

"28/07/2014 6:14:44 PM: 0017 X_ReloadTime:"

"28/07/2014 6:14:44 PM: 0018 LOAD"

"28/07/2014 6:14:44 PM: 0019 NOW() AS StartTime,"

"28/07/2014 6:14:44 PM: 0020 OSUSER() AS ReloadUser"

"28/07/2014 6:14:44 PM: 0021 AUTOGENERATE(1)"

"28/07/2014 6:14:44 PM: 2 fields found: StartTime, ReloadUser, 1 lines fetched"

"28/07/2014 6:14:44 PM: 0027 REFERENCE_TABLE:"

"28/07/2014 6:14:44 PM: 0028 MAPPING"

"28/07/2014 6:14:44 PM: 0029 LOAD"

"28/07/2014 6:14:44 PM: 0030 FIELD_VALUE AS STATUS_FLG."

"28/07/2014 6:14:44 PM: 0031 VALUE_NAME"

"28/07/2014 6:14:44 PM: 0032 FROM \\PROD\QVD\REFERENCE_TABLE.qvd (qvd)"

"28/07/2014 6:14:44 PM: 0033 WHERE FIELD_NAME='STATUS_FLG'"

"28/07/2014 6:14:44 PM: 2 fields found: SA_STATUS_FLG, VALUE_NAME, 8 lines fetched"

"28/07/2014 6:14:44 PM: 0036 ACCOUNTS:"

"28/07/2014 6:14:44 PM: 0037 MAPPING"

"28/07/2014 6:14:44 PM: 0038 LOAD"

"28/07/2014 6:14:44 PM: 0039 ACCOUNT_ID,"

"28/07/2014 6:14:44 PM: 0040 BILL_ID"

"28/07/2014 6:14:44 PM: 0041 FROM \\PROD\QVD\ACCOUNTS.qvd (qvd)"

"28/07/2014 6:14:44 PM: 2 fields found: ACCOUNT_ID, BILL_ID, 1,345,238 lines fetched"

"28/07/2014 6:14:44 PM: 0041 FROM \\PROD\XLS\ExcelSource1.xls"

"28/07/2014 6:14:44 PM: 0041 FROM \\PROD\XLS\ExcelSource2.xls"

"28/07/2014 6:14:44 PM: 0041 FROM \\PROD\XLS\ExcelSource3.xls"

]

Where WildMatch(LogLines,'*.qvd*','*.xls*');

hope this helps

regards

Marco

Re: Re: Extract the Inputs from the Log Files in a Directory

MarcoWedel — Sat, 04 Oct 2014 22:58:42 GMT

Or to load all distinct source file entries (qvd or xls) from all *.log files within the same folder this application is opened from:

LOAD Distinct
  FileName() as LogFile,
  RangeMaxString(SubField(TextBetween([@1:n],'\','.qvd'),'\',-1)&'.qvd',
                SubField(TextBetween([@1:n],'\','.xls'),'\',-1)&'.xls'
              ) as SourceFile
FROM [*.log] (fix, unicode)
Where WildMatch([@1:n],'*.qvd*','*.xls*');

hope this helps

regards

Marco

Re: Extract the Inputs from the Log Files in a Directory

MarcoWedel — Sat, 04 Oct 2014 23:32:40 GMT

you might also wish to try the governance dashboard to extract information about the source files from your QV applications (and much more...):

The QlikView Governance Dashboard | Qlik

hope this helps as well

regards

Marco

Re: Extract the Inputs from the Log Files in a Directory

microwin88x — Sun, 05 Oct 2014 02:24:38 GMT

Hello Marco,

This is what I was looking for.

Thank you very much!

Re: Re: Extract the Inputs from the Log Files in a Directory

MarcoWedel — Sun, 05 Oct 2014 07:50:13 GMT

you're welcome

regards

Marco

Re: Re: Extract the Inputs from the Log Files in a Directory

Aravindvunnam — Wed, 19 Apr 2023 11:36:24 GMT

Hi @MarcoWedel

i tried the same in qlik sense but it unable to fetch the qvd & Xls Details from the log file.

i get results as Source file =.qvd Or .xls (only extensions) with out file name.

can you please suggest me on it.

Thanks & Reagrds;

Aravind.