qlikviewqliksenseqlik-expression

Compare the data sets coming from two set expression in Qliksense


I have a data set in the columns as follows:

Reporting Month Object ID Flag

Flag column can have values as 1 or 0. Reporting Month column has data for May & June.

I want the list of all Object ID where the Flag is 1 in June and was not 1 in May.

I want to achieve this on Qlik's front end through an expression.

I tried using set expression but I am unable to get through the final output.

I created two variables:

Is there a way to compare two data sets (arrays) / strings in qliksense?


Solution

  • If I get your question correctly then your data looks sort of like this:

    Data:
    Load [Object ID], Flag, Date#([Month], 'MM-YYYY') as [Reporting Month]
    Inline [
    Object ID,  Flag, Month
    72, 0, 05-2023 
    83, 1, 05-2023
    99, 0, 05-2023
    12, 1, 05-2023
    43, 0, 05-2023
    23, 0, 05-2023
    72, 1, 06-2023
    83, 0, 06-2023
    99, 1, 06-2023
    12, 0, 06-2023
    43, 0, 06-2023
    ];
    

    And you are looking for a concatenation of Object IDs like:

    Concat({$<[Flag]={'1'}>*<[Object ID] = E({<[Reporting Month] = {'05-2023'}, [Flag]={'1'}>})>} Distinct [Object ID],', ')
    

    Which results in enter image description here

    as Object IDs 99 and 72 are the only ones in my dummy data with Flag = 1 in June but Flag = 0 in May

    The set expression basically creates the intersection of all Object Ids with Flag = 1 and those Object IDs that DON'T have Flag = 1 in May. The E stands for "Exclusion".