Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 - 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
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;
@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