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 IDs like:
Concat({$<[Flag]={'1'}>*<[Object ID] = E({<[Reporting Month] = {'05-2023'}, [Flag]={'1'}>})>} Distinct [Object ID],', ')
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".