Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
lessassy
Creator
Creator

compare two sets of records from two different years-Month to date

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 ?

So basically it's 

ID = Previous(ID) and Previous(Value) > 0 and Value >0 and Current Year & Month number compare to previous year with the exact same month number

Anyone can help me ?

Labels (2)
3 Replies
MayilVahanan

Hi @lessassy 

Try like below

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
];

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 Temp order by ID, Year, Month;

DROP Table Temp;

Screenshot:

MayilVahanan_0-1605770117365.png

 

Thanks & Regards,
Mayil Vahanan R
lessassy
Creator
Creator
Author

Thanks for your answer. I don't understand the year = previous(year)+1 because the other fields are writen this way:

Field = previous(FIeld).

Thanks in advance

lessassy
Creator
Creator
Author

And when i use your script for some reason i get zero value when i apply it to my values.

TEMP_EFF_PERMANENT:
NoConcatenate LOAD
ANNEE,
MOIS,
MATRICULE,
EFF_FINMOIS

Resident V_DWHRH_RH_EFF_PERMANENT;
DROP TABLE [V_DWHRH_RH_EFF_PERMANENT];


EFF_PERMANENT:
NoConcatenate Load *,
if(MATRICULE = Previous(MATRICULE) and ANNEE = Previous(ANNEE)+1
and MOIS = Previous(MOIS) and EFF_FINMOIS >0 and Previous(EFF_FINMOIS)>0, 1, 0) as PermanentStaff
Resident TEMP_EFF_PERMANENT
Order by ANNEE, MOIS, MATRICULE;

DROP TABLE TEMP_EFF_PERMANENT;

 

And when i put it in a where clause i get no values.

Idon't understand why it doesn't work.

Could you help me please ? Thanks.

I think it doesn't recognize the previous year