Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Shubham_Deshmukh
Specialist
Specialist

If ID has entries in previous months then create flags

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

Labels (4)
1 Reply
swuehl
MVP
MVP

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;