topic SV:Set Analysis Expressions in QlikView

Set Analysis Expressions

nicolett_yuri — Fri, 10 Sep 2010 15:18:36 GMT

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]

Set Analysis Expressions

nicolett_yuri — Fri, 10 Sep 2010 19:00:59 GMT

A sample application is attached. Can anyone help me?

Set Analysis Expressions

nicolett_yuri — Mon, 13 Sep 2010 12:25:53 GMT

I can post more examples if my question was confusing

SV:Set Analysis Expressions

gandalfgray — Mon, 13 Sep 2010 13:58:59 GMT

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)

Set Analysis Expressions

Mon, 13 Sep 2010 15:39:07 GMT

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

Set Analysis Expressions

nicolett_yuri — Thu, 16 Sep 2010 21:35:17 GMT

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