compare two sets of records from two different years

[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 with value >0 and appear also in current year 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 ?

Anyone can help me ?

[Kushal_Chawda]

how you are getting 1 for ID 144?

[Kushal_Chawda]

also as per your condition, for ID 188, how you are getting 1 because for current year value is 0

[Taoufiq_Zarra]

and for 188

why its 1 in 2020=0 ?

[lessassy]

Hello there,

sorry for the bad number. those datas were just an example. And while make it the exemple i made a mistake my bad.

Now it is solved (the sample of data) but not the issue ?

Anyone know how i could write it in a script ?

Maybe using previous or exist function.

I don't really know because the issue is that i have lots of datas (5K row approximately).

SO for each ID, the syntax would have to check if the id exist in both current and previous year. And if there is value for the both year so it is permanent staff.

Thanks in advance

[Kushal_Chawda · Accepted solution]

try this

Data:
load * Inline [
ID,Year,Value
186,2020,33
188,2020,10
155,2020,10
187,2020,15
144,2020,0
186,2019,25
188,2019,78
155,2019,0
187,2019,25
144,2019,0
];

Left Join (Data)
Load ID,
     1 as Permanent_staff
Resident Data
where ID=Previous(ID) and Previous(Value)>0 and Value>0
Order by ID, Year;