Search code examples
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:

  • Concat({<[Flag]={'1'}>} Distinct [ObjectID],', ')

  • Concat({<[Flag]={'1'},[Reporting Month]={"$(vPreviousMonth)"}>} Distinct [ObjectID],', ')

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".