IF THEN LOGIC Incorrect results

[CK_WAKE]

Hi There,

I'm encountering issues building a report in Qlik Sense using an IF THEN logic. The logic I'm applying produces incorrect results, although the same logic works as expected in Python.

My goal is to evaluate the following conditions:

IF WORK_YEAR is less than or equal to FAILURE_YR AND WORK_DESC is equal to MEASURE_DESC AND Equipment Type matches any of the values in the fields Measure_1 to Measure_10 THEN the result should be 'Yes', ELSE 'No'.

I've tried various combinations, but the output in Qlik Sense is consistently incorrect.

I would appreciate your assistance in resolving this.

I have attached sample data.

analysis:
load *,
IF(WORK_YEAR <= FAILURE_YR, 'Yes', 'No') as Year_Check,
IF(WORK_DESC = MEASURE_DESC, 'Yes', 'No') as JOB_DESCK_Check,

IF(  (WORK_YEAR <= FAILURE_YR AND WORK_DESC = MEASURE_DESC) AND
        Match(
            TRIM([Equipment Type]), 
            TRIM(Measure_1), TRIM(Measure_2), TRIM(Measure_3), 
            TRIM(Measure_4), TRIM(Measure_5), TRIM(Measure_6), 
            TRIM(Measure_7), TRIM(Measure_8), TRIM(Measure_9), 
            TRIM(Measure_10)
        ), 
        'Yes', 
        'No'
    ) AS IN_THE_PLAN_MEASURE1,

    IF(
    WORK_YEAR <= FAILURE_YR
    AND WORK_DESC = MEASURE_DESC
    AND (
        UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_1))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_2))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_3))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_4))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_5))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_6))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_7))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_8))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_9))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(Measure_10))
    ),
    'Yes',
    'No'
) AS IN_THE_PLAN_MEASURE2,

IF(
     WORK_YEAR <= FAILURE_YR
    AND WORK_DESC = MEASURE_DESC
    AND (
        UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_1], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_2], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_3], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_4], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_5], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_6], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_7], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_8], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_9], chr(160), '')))
        OR UPPER(TRIM([Equipment Type])) = UPPER(TRIM(REPLACE([Measure_10], chr(160), '')))
    ),
    'Yes',
    'No'
) AS IN_THE_PLAN_MEASURE3


Resident Sheet1;
drop table Sheet1;

[rubenmarin · Accepted solution]

Hi, the space in equipment is Chr(32) and the measures are using Chr(160), you can try loading all measures as:

Replace(Measure_1,Chr(160),Chr(32)) as Measure_1,

[CK_WAKE]

Thanks for the help, it worked.