If ID has entries in previous months then create flags

[Shubham_Deshmukh]

Hi Qlikers,

I want to check if same ID has appeared earlier months as well or not, would prefer to do this in script.
Need Flag : Yes / No
Data range : Last 3 months
Business Logic :
1.If ID has >=2 entries in March, then YES
2.If ID has 1 entry in March and 1 entry in Jan or Feb then YES
3 If ID has 1 entry in all 3 months then YES
4 If ID has entry in Jan and Feb not in Mar then NO

Sample Data  

Date	Month	ID	Name	Flag (required O/P)
24/3/2022	Mar-22	1	Rajesh	YES
17/3/2022	Mar-22	1	Rajesh	YES
5/3/2022	Mar-22	2	Jack	YES
15/2/2022	Feb-22	2	Jack	YES
10/1/2022	Jan-22	3	Robert	YES
23/3/2022	Mar-22	3	Robert	YES
19/1/2022	Jan-22	4	Samuel	NO
1/2/2022	Feb-22	4	Samuel	NO

 
@sunny_talwar @swuehl @jagan 

Regards,
Shubham

[swuehl]

Maybe something like

//Correct for my date settings to load Date as a Qlik date
INPUT:
LOAD *, Monthname(Date#(Date,'DD/M/YYYY')) as myMonth
INLINE [
Date Month ID Name Flag (required O/P)
24/3/2022 Mar-22 1 Rajesh YES
17/3/2022 Mar-22 1 Rajesh YES
5/3/2022 Mar-22 2 Jack YES
15/2/2022 Feb-22 2 Jack YES
10/1/2022 Jan-22 3 Robert YES
23/3/2022 Mar-22 3 Robert YES
19/1/2022 Jan-22 4 Samuel NO
1/2/2022 Feb-22 4 Samuel NO
] (delimiter is '\t');

// You can get the latest Month from INPUT
Let vMaxMonth = Num(Makedate(2022,3));

// Rule: Either >= 2 records in max Month or at least 1 record in max month and an number of records in previous months
LOAD ID,
If( (Sum(myMonth = $(vMaxMonth))*-1) >= 2
or (Sum(myMonth = $(vMaxMonth))*-1 = 1 and Sum(myMonth <$(vMaxMonth)))*-1>0,
Dual('YES',1),
Dual('NO',0)) as myFlag
Resident INPUT
GROUP BY ID;