Hello experts,
I'm trying to create an application that analyzes the log files you can get QV to generate.
I'm extracting startdate and time and enddate and time along with any errors that occured while loading.
My problem in the script below occurs in the table Post_Dynamic:
I need the firstvalue in Unique_Id to be duplicated for all rows.
Otherwise I do not know from what date the errors occur since this is only a part of the script.
I have tried using both firstvalue() and minstring() but the result is the same, i.e. both values in this field are still remaining.
Anyone that can tell me what I'm doing wrong?
Pre_Dynamic:
LOAD
If(left(@27:n, 11) = 'Execution s', @1:10) as Date_Start,
If(left(@27:n, 11) = 'Execution f', @1:10) as Date_End,
If(left(@27:n, 11) = 'Execution s', @12:19) as Time_Start,
If(left(@27:n, 11) = 'Execution f', @12:19) as Time_End,
if(trim(left(@27:n, 7)) = 'Error:', @27:n) as Error,
if(trim(left(@27:n, 7)) = 'Error:', rowno()-1) as Error_number,
filebasename() as Application,
right(filedir(),3) as Company
//FROM U:\PCD\*.log
FROM D:\QlikViewDocuments\PCD\Marknad.qvw.log
(fix, codepage is 1252)
WHERE trim(left(@27:n, 7))= 'Error:'
or left(@27:n, 9) = 'Execution';
Dynamic:
NoConcatenate Load
max(Date_Start) as Date_Start,
max(Date_End) as Date_End,
max(Time_Start) as Time_Start,
max(Time_End) as Time_End,
max(Date_Start)&'-'& max(Time_Start) &'-'& Application &'-'& Company as Unique_Id,
Error,
Error_number,
Application,
Company
Resident Pre_Dynamic
group by Application, Company, Error, Error_number;
drop table Pre_Dynamic;
Post_Dynamic:
NoConcatenate Load
Date_Start,
Date_End,
Time_Start,
Time_End,
FirstValue(Unique_Id) as Unique_Id,
Error,
Error_number,
Application,
Company
Resident Dynamic
group by Date_Start, Date_End, Time_Start, Time_End, Error, Error_number, Application, Company;
drop table Dynamic;
br
Martin
