Re: Comparing Field to a Variable - Qlik Community

Report Inappropriate Content · ‎2016-06-21

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

swuehl · ‎2016-06-21

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?

Report Inappropriate Content · ‎2016-06-22

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

Report Inappropriate Content · ‎2016-06-22

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

& EVENT_DATE & '|' & Year("EVENT_DATE") & '|' & Month("EVENT_DATE") & '|' & Day("EVENT_DATE") & '|' & Hour("EVENT_TIME")

& '|' & Hour("EVENT_TIME") + 2 as key,

& 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

& 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

& 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?

swuehl · ‎2016-06-22

Uploading a Sample