Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
In my script it is this
// LIB CONNECT TO 'BO_DATABASE';
// LOAD YEAR,
// MONTH,
// ID,
// Value,
// if(ID = Previous(ID) and ANNEE = Previous(ANNEE)+1
// and MONTH = Previous(MONTH) and Value >0 and Previous(Value)>0, 1, 0) as PermanentStaff;
// [DATA_TABLE]:
// SELECT "YEAR",
// "MONTH",
// "ID",
// "Value"
// FROM SAP_BO_DATABASE;
// // UNTITITLE:
// // 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 [DATA_TABLE];
// // Drop Table [DATA_TABLE];
But i get PermanentStaff field with zero value everytime.
Where i sample small data like this,
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
];
I get the field "permanent staff" right but it doesn't come from a SAP BO Database.
ANyone could help me.
try this:
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
];
//this builds the list of permanent IDs
NoConcatenate
T: load * Resident Temp;
inner join (T) load ID, Year as LastYear, Month as LastMonth, Value as LastYearValue Resident T;
NoConcatenate Permanent: load ID, Year, Month, 1 as Permanent Resident T where Value > 0 and LastYearValue>0 and LastYear=Year-1 and LastMonth=Month;
left join (Temp) load ID, Year, Month, Permanent Resident Permanent;
drop table T, Permanent;
just realized month should be the same so a more efficient join will be:
inner join (T) load ID, Year as LastYear, Month, Value as LastYearValue Resident T;
and you wont need the check
and LastMonth=Month;
Thanks for the update and quick reply. I'll be sure to keep an eye on this thread. Looking for the same issue. Bumped into your thread. Thanks for Pizza Hut Survey . Looking forward for solution .