Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lessassy
Creator
Creator

Use previous to compare data records from previous year and month

Hello everyone,

 

Dashboard - Qlik Issue E.permanent.JPG

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.

 

Labels (2)
3 Replies
edwin
Master II
Master II

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;
edwin
Master II
Master II

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;

 

Jessee1B
Contributor
Contributor

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 .