Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.03.10.13/teste.qvw]
Hey Yuri.
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 valid
PositionValues:
LOAD * INLINE [
Position, INI_KEY_DATE,FIN_KEY_DATE, salary
1, 200801, 201001, 6
1, 201002, 999999, 8];
// activites done and their dates
Activities:
LOAD * INLINE [
Employee, Position, Date_Activiry
Fulano1, 1, 2010001
Fulano1, 1, 2010007
];
// join both of them
XList_1:
Load Postiion, INI_KEY_DATE,FIN_KEY_DATE, salary
Resident PositionValues;
join
Load Employee, Position, Date_Activiry
Resident Activities;
// calculate wich ones are valid
Xlist_2:
NOCONCATENATE
LOAD *, if((Date_Activiry>=INI_KEY_DATE and Date_Activiry<=FIN_KEY_DATE), 1, -1) AS ValidLine
RESIDENT XList_1;
// The final Table
ActivitiesValue:
Load Employee, Position, Date_Activiry, salary
Resident Xlist_2
Where ValidLine = 1;
// get rid of the rest
drop table XList_1;
drop table XList_2;
Just made on the Notepad, so it may have some ; or : problem. But the idea is there.
Cheers
Adriano
A sample application is attached. Can anyone help me?
I can post more examples if my question was confusing
Hi Yuri
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)
Hey Yuri.
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 valid
PositionValues:
LOAD * INLINE [
Position, INI_KEY_DATE,FIN_KEY_DATE, salary
1, 200801, 201001, 6
1, 201002, 999999, 8];
// activites done and their dates
Activities:
LOAD * INLINE [
Employee, Position, Date_Activiry
Fulano1, 1, 2010001
Fulano1, 1, 2010007
];
// join both of them
XList_1:
Load Postiion, INI_KEY_DATE,FIN_KEY_DATE, salary
Resident PositionValues;
join
Load Employee, Position, Date_Activiry
Resident Activities;
// calculate wich ones are valid
Xlist_2:
NOCONCATENATE
LOAD *, if((Date_Activiry>=INI_KEY_DATE and Date_Activiry<=FIN_KEY_DATE), 1, -1) AS ValidLine
RESIDENT XList_1;
// The final Table
ActivitiesValue:
Load Employee, Position, Date_Activiry, salary
Resident Xlist_2
Where ValidLine = 1;
// get rid of the rest
drop table XList_1;
drop table XList_2;
Just made on the Notepad, so it may have some ; or : problem. But the idea is there.
Cheers
Adriano
Thank you. I found another way to solve my problem by using the proper interval match.