Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
please provide sample data and expected result.
thanks
regards
Marco
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:
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.
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
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
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
Hello Marco,
This is what I was looking for.
Thank you very much!
you're welcome
regards
Marco
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.