Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I wonder if you could help me with the following: I have loaded data from a database in Qlikview using preceding loads. Now, I have three tables that are connected with a single key.
I have calculated the standard deviation for a field in one of these tables.
Based on this standard deviation, I would like to identify whether the value in the field is smaller or bigger than the standard deviation. When smaller, it should say 'OK', when larger 'ERROR'.
I have tried the following:
- I adapted the logic as described on http://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/ to get rid of synthetic tables (mimicking with preceding LOAD and SQL statements) One of the dimension tables is DetectorBase
- at the end of my script; I calculated the Standard Deviation and put it in a variable
CalStdDevCpu:
LOAD Stdev(TOTAL_INDB2_CPU) as StDevCpu
Resident DetectorBase
;
let vStDevCpu = peek('StDevCpu',0,CalStdDev);
All of this was ok
- Finally, I tried to categorize my the values from my original table as follows:
Categorization:
LOAD key
,if(Num#(TOTAL_INDB2_TIME) >= Num#($(vStDevTime2)),'ERROR','OK') as CatDb2Time
Resident DetectorBase
;
This yielded as result, a single row with CatDb2Time = 'ERROR' and all nulls for the other fields and all other rows with CatDB2Time being null and all other rows filled out. With INNER JOIN, I couldn't read the table and with an OUTER JOIN, I got strange results to...
Does anyone have a clue on how to proceed?
Many thanks!
Ludovic
I don't understand how
let vStDevCpu = peek('StDevCpu',0,CalStdDev);
is related to
Categorization:
LOAD key
,if(Num#(TOTAL_INDB2_TIME) >= Num#($(vStDevTime2)),'ERROR','OK') as CatDb2Time
Resident DetectorBase;
Variable name and doesn't seem to match (not that I would know the answer to your problem if these would match)
Could you create a small sample QVW and upload to this thread?
Hi,
Sorry, I made a mistake, I meant
Categorization:
LOAD key
,if(Num#(TOTAL_INDB2_CPU) >= Num#($(vStDevCpu)),'ERROR','OK') as CatDb2Cpu
Resident DetectorBase
;
(I had two similar variables)
Best regards,
Ludovic
PS: I'll attach my qvw once I found out where I can do this
Hi,
I cannot upload my complete qvw due to security restrictions, but I can share the relevant part of my script:
DetectorBase:
//First (50)
LOAD
"ARCH_WTIME" as "WAIT_FOR_ARCHIVE_LOG",
"ARCRD_WTIME" as "WAIT_FOR_TAPE",
"AT_WTIME" as "WAIT_FOR_AUTONOMOUS_TRANSACTIONS",
"CLAIM_WTIME" as "WAIT_FOR_CLAIMS",
"COMMITS" as "NUMBER_OF_COMMITS",
"DRAIN_WTIME" as "WAIT_FOR_DRAINS",
"DSS_WTIME" as "WAIT_FOR_DATASPACE_MANAGER",
"GETPAGE" as "NUMBER_OF_GETPAGES",
"INDB2_CPU" as "TOTAL_INDB2_CPU",
"INDB2_TIME" as "TOTAL_INDB2_TIME",
"INTERVAL_END" as "INTERVAL_END_TS",
"INTERVAL_START" as "INTERVAL_START_TS",
"IO_WTIME" as "WAIT_FOR_IO",
"LLOCK_WTIME" as "WAIT_FOR_L_LOCKS",
"LOB_WTIME" as "WAIT_FOR_LOB",
"LOCK_WTIME" as "WAIT_FOR_LOCKS",
"LOG_WTIME" as "WAIT_FOR_LOG",
"OCS_WTIME" as "WAIT_FOR_DATASET",
"ORIO_WTIME" as "WAIT_FOR_OTHER_READ_IO",
"OWIO_WTIME" as "WAIT_FOR_OTHER_WRITE_IO",
"PLOCK_WTIME" as "WAIT_FOR_P_LOCKS",
"SERV_WTIME" as "WAIT_FOR_SERVICES",
"SLS_WTIME" as "WAIT_FOR_SYSLGRNG",
"SQL_CALLS" as "NUMBER_OF_QUERIES",
"STMT#" as "QUERY_NUMBER",
"TOTAL_WAITCNT" as "NUMBER_OF_WAITS",
"TOTAL_WAITTIM" as "TOTAL_WAIT_TIME",
"UNACC_TIME" as "UNACCOUNTED_TIME",
SUBSYSTEM & '|' & if (Len(Trim(CORRELATION_ID))=0,'UNKNOWN',CORRELATION_ID) & '|' & if (Len(Trim(PLAN_NAME))=0,'UNKNOWN',PLAN_NAME) & '|'
& if (Len(Trim(COLLECTION_ID))=0,'UNKNOWN',COLLECTION_ID) & '|' & if (Len(Trim(PACKAGE))=0,'UNKNOWN',PACKAGE) & '|'
& Date("INTERVAL_START") & '|' & Year("INTERVAL_START") & '|' & Month("INTERVAL_START") & '|' & Day("INTERVAL_START") & '|'
& Hour("INTERVAL_START") & '|' & Hour("INTERVAL_START")+ 2 as key;
SQL SELECT
"ARCH_WTIME",
"ARCRD_WTIME",
"AT_WTIME",
"CLAIM_WTIME",
"COLLID" as COLLECTION_ID,
COMMITS,
CORRID as CORRELATION_ID,
"DRAIN_WTIME",
"DSS_WTIME",
GETPAGE,
"GLOCK_WTIME",
"INDB2_CPU",
"INDB2_TIME",
"INTERVAL_END",
"INTERVAL_START",
"IO_WTIME",
"LLOCK_WTIME",
"LOB_WTIME",
"LOCK_WTIME",
"LOG_WTIME",
"OCS_WTIME",
"ORIO_WTIME",
ORIOWCNT,
"OTS_WTIME",
"OWIO_WTIME",
PLANNAME as PLAN_NAME,
"PLATCH_WTIME",
"PLOCK_WTIME",
"PROGRAM" as PACKAGE,
"SERV_WTIME",
"SLS_WTIME",
"SMSG_WTIME",
"SQL_CALLS",
SSID as SUBSYSTEM,
"STMT#",
"TOTAL_WAITCNT",
"TOTAL_WAITTIM",
"UNACC_TIME"
FROM MY.MONITOR_TABLE
WHERE "INTERVAL_START" > CURRENT TIMESTAMP - 3 DAYS
FETCH FIRST 100000 ROWS ONLY;
Link:
LOAD
SUBSYSTEM & '|' & CORRELATION_ID & '|' & PLAN_NAME & '|' & COLLECTION_ID & '|' & PACKAGE & '|'
& EVENT_DATE & '|' & Year("EVENT_DATE") & '|' & Month("EVENT_DATE") & '|' & Day("EVENT_DATE") & '|' & Hour("EVENT_TIME")
& '|' & Hour("EVENT_TIME") + 2 as key,
SUBSYSTEM & '|' & CORRELATION_ID & '|' & PLAN_NAME & '|' & COLLECTION_ID & '|' & PACKAGE & '|'
& EVENT_DATE & '|' & Year("EVENT_DATE") & '|' & Month("EVENT_DATE") & '|' & Day("EVENT_DATE") & '|' & Hour("EVENT_TIME")
& '|' & Hour("EVENT_TIME") + 2 as tempKey,
SUBSYSTEM,
CORRELATION_ID,
PLAN_NAME,
COLLECTION_ID,
PACKAGE,
EVENT_DATE,
Year("EVENT_DATE") as EVENT_YEAR,
Month("EVENT_DATE") as EVENT_MONTH,
Day("EVENT_DATE") as EVENT_DAY,
Hour("EVENT_TIME") as EVENT_INTERVAL_START_HOUR,
Hour("EVENT_TIME") + 2 as EVENT_INTERVAL_END_HOUR
;
SQL SELECT DL."SUBSYSTEM" as "SUBSYSTEM"
, J."CORRELATION_ID" as "CORRELATION_ID"
, J."PLAN_NAME" as "PLAN_NAME"
, J."COLLECTION_ID" as "COLLECTION_ID"
, COALESCE(J."PACKAGE_NAME",'UNKNOWN') as "PACKAGE"
, TIME(DL.TIMESTAMP) as "EVENT_TIME"
, DATE(DL.TIMESTAMP) as "EVENT_DATE"
FROM "SCVBEX"."JOBS" J
INNER JOIN "SCVBEX"."DEADLOCKS" DL
ON J."JOB_ID" = DL."JOB_ID_VICTIM"
INNER JOIN "SCVBEX"."STATEMENTS" STMT
ON DL."SUBSYSTEM" = STMT."SUBSYSTEM"
AND DL."STATEMENT_ID_VICTIM" = STMT."STATEMENT_ID"
DetectorLink:
LOAD
SUBSYSTEM as SUBSYSTEM_T
,if (Len(Trim(CORRELATION_ID))=0,'UNKNOWN',CORRELATION_ID) as CORRELATION_ID_T
,if (Len(Trim(PLAN_NAME))=0,'UNKNOWN',PLAN_NAME) as PLAN_NAME_T
,if (Len(Trim(COLLECTION_ID))=0,'UNKNOWN',COLLECTION_ID) as COLLECTION_ID_T
,if (Len(Trim(PACKAGE))=0,'UNKNOWN',PACKAGE) as PACKAGE_T
,Date("INTERVAL_START") as EVENT_DATE_T
,Year("INTERVAL_START") as EVENT_YEAR_T
,Month("INTERVAL_START") as EVENT_MONTH_T
,Day("INTERVAL_START") as EVENT_DAY_T
,Hour("INTERVAL_START") as EVENT_INTERVAL_START_HOUR_T
,Hour("INTERVAL_START")+ 2 as EVENT_INTERVAL_END_HOUR_T;
SQL SELECT
SSID as SUBSYSTEM,
CORRID as CORRELATION_ID,
PLANNAME as PLAN_NAME,
"COLLID" as COLLECTION_ID,
"PROGRAM" as PACKAGE,
INTERVAL_START as INTERVAL_START
FROM MY.MONITOR_TABLE
WHERE "INTERVAL_START" > CURRENT TIMESTAMP - 3 DAYS
FETCH FIRST 100000 ROWS ONLY;
Concatenate(Link)
LOAD
Distinct
SUBSYSTEM_T & '|' & CORRELATION_ID_T & '|' & PLAN_NAME_T & '|' & COLLECTION_ID_T & '|' & PACKAGE_T & '|'
& EVENT_DATE_T & '|' & EVENT_YEAR_T & '|' & EVENT_MONTH_T & '|' & EVENT_DAY_T & '|' & EVENT_INTERVAL_START_HOUR_T
& '|' & EVENT_INTERVAL_END_HOUR_T as key,
SUBSYSTEM_T as SUBSYSTEM,
CORRELATION_ID_T as CORRELATION_ID,
PLAN_NAME_T as PLAN_NAME,
COLLECTION_ID_T as COLLECTION_ID,
PACKAGE_T as PACKAGE,
EVENT_DATE_T as EVENT_DATE,
EVENT_YEAR_T as EVENT_YEAR,
EVENT_MONTH_T as EVENT_MONTH,
EVENT_DAY_T as EVENT_DAY,
EVENT_INTERVAL_START_HOUR_T as EVENT_INTERVAL_START_HOUR,
EVENT_INTERVAL_END_HOUR_T as EVENT_INTERVAL_END_HOUR
Resident DetectorLink
Where not Exists(tempKey,SUBSYSTEM_T & '|' & CORRELATION_ID_T & '|' & PLAN_NAME_T & '|' & COLLECTION_ID_T & '|' & PACKAGE_T & '|'
& EVENT_DATE_T & '|' & EVENT_YEAR_T & '|' & EVENT_MONTH_T & '|' & EVENT_DAY_T & '|' & EVENT_INTERVAL_START_HOUR_T
& '|' & EVENT_INTERVAL_END_HOUR_T) ;
Drop Field tempKey from Link;
CalStdDevCpu:
LOAD Stdev(TOTAL_INDB2_CPU) as StDevCpu
,Stdev(TOTAL_INDB2_TIME) as StDevTime
Resident DetectorBase
;
let vStDevCpu = peek('StDevCpu',0,CalStdDev);
let vStDevTime = peek('StDevTime',0,CalStdDev);
let vStDevCpu2 = peek('StDevCpu',0,CalStdDev)*2;
let vStDevTime2 = peek('StDevCpu',0,CalStdDev)*2;
Categorization:
LOAD key
// , if(if(ISNULL(TOTAL_INDB2_CPU)=0,TOTAL_INDB2_CPU,0) >= Num#($(vStDevCpu2)), 'ERROR', 'OK') as catDb2Cpu
// ,if(Num#(TOTAL_INDB2_TIME) >= Num#($(vStDevTime2)),'ERROR','OK') as CatDb2Time
,if(TOTAL_INDB2_CPU>Num#($(vStDevCpu2)),'ERROR',if(TOTAL_INDB2_CPU>Num#($(vStDevCpu)),'WARNING','OK')) as catDb2Cpu
,if(TOTAL_INDB2_TIME>Num#($(vStDevTime2)),'ERROR',if(TOTAL_INDB2_TIME>Num#($(vStDevTime)),'WARNING','OK')) as CatDb2Time
Resident DetectorBase
;
EXIT Script;
The part in bold and italic does not work as I expected. Could you advise?