Load Script - How to set a flag based on a previous value in the table

[WeePecky]

Greetings,

I have data that reports by Quarter.

If an item exists in a prior quarter I want to set a flag on the current quarter.

For example:

ID	Value	Quarter	Previous Quarter Flag
111	59	201701	0
222	61	201701	0
333	100	201702	0
111	300	201702	1

The previous quarter flag with a value of 1 in the table above indicates that the ID 111 had been reported in the previous quarter. I wish to do this in the load script.

Any assistance appreciated.

Kindest regards

Wee.

[lironbaram]

hi 

this script should work 

Table1:
load ID,Value,Quarter
From XXX;
////////////////flaging each quarter for each ID if the id was reported in previous Quarter /////////

Table2:
Load *,
     if(previous(ID)=ID and if(right(Quarter,1)=4,if(Quarter-previous(Quarter)=97,1,0),if(Quarter-previous(Quarter)=1,1,0)),0) as Flag
Resident Table1
order by ID,Quarter;

drop table Table1;

[WeePecky · Accepted solution]

@lironbaram - thanks!

That was enough to get me across the line. 
I added a column for year to aid the calculation, because the quarter 1 comes after quarter 4.

The final script was as follows:

Load
 *,
    if(
     previous(ID)=ID 
     and if(Quarter = 1 and previous(Quarter) = 4 and Year = Previous(Year) + 1, 1,
          if(Quarter - 1 = previous(Quarter), 1, 0)
              ),1,0
    ) as long_term_medication_flag

Your assistance is greatly appreciated.

Cheers