compare two sets of records from two different years-Month to date

[lessassy]

Hello everyone ,

Basically what i want to do about this data set is the following one

If the sameID appears in the previous year and month  with value >0 and appear also in current year with the previous month as well with value > 0 then it gets '1' in the column permanent staff.

I would like to do it in the script just to have a brand new column permanent staff but i don't know how i could do that ?

So basically it's 

ID = Previous(ID) and Previous(Value) > 0 and Value >0 and Current Year & Month number compare to previous year with the exact same month number

Anyone can help me ?

[MayilVahanan]

Hi @lessassy 

Try like below

Temp:
Load * Inline
[
ID, Year, Month, Value
180, 2019, 1, 55
181, 2019, 2, 0
182, 2019, 3, 65
183, 2019, 4, 0
184, 2019, 5, 65
180, 2020, 1, 58
181, 2020, 2, 87
182, 2020, 3, 47
183, 2020, 4, 88
184, 2020, 5, 89
];

Load *, if(ID = Previous(ID) and Year = Previous(Year)+1 and Month = Previous(Month) and Value >0 and Previous(Value)>0, 1, 0) as PermanentStaff
Resident Temp order by ID, Year, Month;

DROP Table Temp;

Screenshot:

[lessassy]

Thanks for your answer. I don't understand the year = previous(year)+1 because the other fields are writen this way:

Field = previous(FIeld).

Thanks in advance

[lessassy]

And when i use your script for some reason i get zero value when i apply it to my values.

TEMP_EFF_PERMANENT:
NoConcatenate LOAD
ANNEE,
MOIS,
MATRICULE,
EFF_FINMOIS

Resident V_DWHRH_RH_EFF_PERMANENT;
DROP TABLE [V_DWHRH_RH_EFF_PERMANENT];

EFF_PERMANENT:
NoConcatenate Load *,
if(MATRICULE = Previous(MATRICULE) and ANNEE = Previous(ANNEE)+1
and MOIS = Previous(MOIS) and EFF_FINMOIS >0 and Previous(EFF_FINMOIS)>0, 1, 0) as PermanentStaff
Resident TEMP_EFF_PERMANENT
Order by ANNEE, MOIS, MATRICULE;

DROP TABLE TEMP_EFF_PERMANENT;

And when i put it in a where clause i get no values.

Idon't understand why it doesn't work.

Could you help me please ? Thanks.

I think it doesn't recognize the previous year