Extract the Inputs from the Log Files in a Directory

[microwin88x]

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!

[MarcoWedel]

please provide sample data and expected result.

thanks

regards

Marco

[microwin88x]

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.

[MarcoWedel]

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

[MarcoWedel · Accepted solution]

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

[MarcoWedel]

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

[microwin88x]

Hello Marco,

This is what I was looking for.

Thank you very much!

[MarcoWedel]

you're welcome

regards

Marco

[Aravindvunnam]

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.