Problem with multiple records when spliting data

[#1]

Hello,

I need to split data depending on the time after the in service date of an employee. The problem I encounter when spliting the data is that I have more than one record with the in service date of an employee available and the split result is a multiple of the actual total data.

The data I want to split is available per employee and date. The total data is available with the simply expression =SUM(Value.Valuefield). When I use the expression =SUM(IF(Date.Valuefield-Employee.InServiceDate>=29,Value.Valuefield,0)) the total value per employee gets multiplier by the number of records with in service dates available for that employee, which is logical, but unwanted. Is there a way to use only one of the employee in service date per employee?

Example:

Available value record (Date.Valuefield, Value.Valuefield, Employee):

26-08-2013, 8, employee1

27-08-2013, 8, employee1

Available employee records with in service date (Employee, Employee.InServiceDate, Month):

employee1, 01-07-2013, July

employee1, 01-07-2013, August

The simple sum expression results in 16

The expression which checks the in service date results in 32

Has anyone an idea? Many thanks!

[rajeshvaswani77]

Hi Raimund,

Please check the attached QVW.

thanks,

Rajesh Vaswani

[#3]

Hi Rajesh,

Thanks for your reaction. I noticed my description is not clear enough. I have a lot of different employees, lots of Values I like to add up and also a lot of records per employee with the Inservice date.

I adjusted the qvw a bit, perhaps that clearifies it.

[#4]

Hello,

I found a solution to the problem. Thanks for your time.

Solution I found: by adding a column "employed" in the employee records, with a 1 in the month in which the employee starts and a 0 in the other months. Now I added an IF to the expression regarding the Employee.InServiceDate.

sum(if(Date.Valuefield-(IF(Employed=1,Employee.InServiceDate,))<29,Value.Valuefield,0))

[#5]

Hi Raimund,

When I have had similar issues in that I have a lower level of granularity in my data than I need for a particular expression I have used peek and order by to give me an identifier for the distinct records that I want.

1) First define your compound key i.e. employee & date in your case I think

2) Perform a resident load and order by your compound key

3) Use the peek function to check for each distinct occurrence of your compound key during the resident load

e.g.

Table 1:

LOAD

     Field 1 & '-' & Field 2               as [%Compound Key],

     Field 1,

     Field 2,

     Field 3,

From xyz.qvd

Table 2:

LOAD

     [%Compound Key],

     Field 1,

     Field 2,

     Field 3,

     If([%Compound Key] = Peek('%Compound Key'),

          0,

          1)                                   as _DistinctCompoundKey

Resident Table 1

Order By [%Compound Key];

This way you can use set analysis expression to sum up and field only for the distinct occurrences of your compound key. i.e

sum({$<_DistinctCompoundKey = {1}>}Field 3) or even

sum(_DistinctCompoundKey) depending on what you want to measure.

I believe this is a more efficient way of accomplishing what you need as it won't consume as much resource when calculating.

Andy

[#6]

Hello Andy,

Many thanks for your extra information!

Raimund