Discussion Board for collaboration related to Creating Analytics for QlikView.
We need to find a way to retrieve a value in a table using set analysis.
In this table we have salary of a employee over the time, we have all the salary on each month.
In a graph we need to show the value of salary of the last salary change.
Go to Solution.
by you TESTE.qvw, it seems you are Brazilian as well.
I have done a similar thing. But I did it on Load as suggested above:
// date range that the value was validPositionValues:LOAD * INLINE [ Position, INI_KEY_DATE,FIN_KEY_DATE, salary 1, 200801, 201001, 6 1, 201002, 999999, 8];
// activites done and their datesActivities:LOAD * INLINE [Employee, Position, Date_ActiviryFulano1, 1, 2010001Fulano1, 1, 2010007];
// join both of themXList_1:Load Postiion, INI_KEY_DATE,FIN_KEY_DATE, salaryResident PositionValues;joinLoad Employee, Position, Date_ActiviryResident Activities;
// calculate wich ones are validXlist_2:NOCONCATENATELOAD *, if((Date_Activiry>=INI_KEY_DATE and Date_Activiry<=FIN_KEY_DATE), 1, -1) AS ValidLineRESIDENT XList_1;
// The final TableActivitiesValue:Load Employee, Position, Date_Activiry, salaryResident Xlist_2Where ValidLine = 1;
// get rid of the restdrop table XList_1;drop table XList_2;
Just made on the Notepad, so it may have some ; or : problem. But the idea is there.
A sample application is attached. Can anyone help me?
I can post more examples if my question was confusing
I would not use set analysis to solve your problem.
I would do something like this:
In the load script:
Use the Colaborador table to build a new table where each Employee has a record with a date interval specifying the salary for that period.
Use that new table to get the employees salary for a given date (performing an interval match)
Thank you. I found another way to solve my problem by using the proper interval match.