Comparing Field to a Variable

[#1]

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]

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?

[#3]

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

[#4]

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?

[swuehl]

Uploading a Sample