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: 
nicolett_yuri

Set Analysis Expressions

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]

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

5 Replies
nicolett_yuri
Author

A sample application is attached. Can anyone help me?

nicolett_yuri
Author

I can post more examples if my question was confusing

gandalfgray
Specialist II
Specialist II

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)

Not applicable

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

nicolett_yuri
Author

Thank you. I found another way to solve my problem by using the proper interval match.