topic Re: Use previous to compare data records from previous year and month in QlikView

Use previous to compare data records from previous year and month

lessassy — Sat, 16 Nov 2024 17:48:15 GMT

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,

I get the field "permanent staff" right but it doesn't come from a SAP BO Database.

ANyone could help me.

Re: Use previous to compare data records from previous year and month

edwin — Sat, 21 Nov 2020 23:19:31 GMT

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;

Re: Use previous to compare data records from previous year and month

edwin — Sun, 22 Nov 2020 00:10:46 GMT

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;

Re: Use previous to compare data records from previous year and month

Jessee1B — Wed, 25 Nov 2020 04:29:11 GMT

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 .