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

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

WeePecky — Thu, 06 Aug 2020 03:19:26 GMT

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.

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

lironbaram — Thu, 06 Aug 2020 05:43:38 GMT

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;

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

WeePecky — Fri, 07 Aug 2020 02:11:37 GMT

@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