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?
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 ID
s like:
Concat({$<[Flag]={'1'}>*<[Object ID] = E({<[Reporting Month] = {'05-2023'}, [Flag]={'1'}>})>} Distinct [Object ID],', ')
as Object ID
s 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 Id
s with Flag = 1
and those Object ID
s that DON'T have Flag = 1
in May. The E stands for "Exclusion".