Set expression: Group of Max Date

[beck_bakytbek]

Hi Folks,

i am a bit confused About of set Expression, i have a following data:

my table does look like:

Date, EmployeeGroup, Employee, Sales

01.01.2019, 4, Alex, 5

01.01.2018, 6, Alex, 6

01.01.2017, 8, Alex, 7

How can i Show the Sales of Alex for Date 01.01.2019 in depence on EmployeeGroup = 4 (in Script). on UI i know how to solve it, but how can i Show that in script?

Does anybody have any idea?

Thanks a lot

Beck

[JordyWegman]

Hi,

If you only want 4, then you can use a where statement for this. 

Jordy

Climber

[beck_bakytbek]

Hi Jordy,

thanks a lot for your responce, can make i depend on Data-Field, i mean, for example Show me the Group of last Date: 01.01.2019, how to make it possible in script?

Do you habe any idea?

[JordyWegman]

Hi,

In this example this would be:

Load
*
Where Date = '01.01.2019'
;
Load * Inline [
Date, EmployeeGroup, Employee, Sales
01.01.2019, 4, Alex, 5
01.01.2018, 6, Alex, 6
01.01.2017, 8, Alex, 7
];

But now you don't get all the other information of 2017/2018 etc.

Jordy

Climber 

[beck_bakytbek]

Hi Jordy,

thanks a lot for your Feedback and help,

my issue is: i have a large table with fieild

Data,                         EmployeeGroup 

01.01.2017 - 31.12.2017, 4

01.01.2018 - 31.12. 2018, 5

01.01.2019 - 31.12.2019, 6

my issue is, to get always the EmployeeGroup of last year, Maybe it is possible to solve with peek-function in script-area?

Thanks a lot

[JordyWegman]

Hi Beck,

Do you also have EmployeeGroups for in the future? Otherwise you can do Max(EmployeeGroup) and you will always have the max one.

Jordy

Climber

[beck_bakytbek]

Hi Jordy,
thanks a lot for your Feedback and time,
i think, i explained not well enough, my issue is: i have always the field of EmployeesGroup and data, i should take always into account the employeeGroup in depence of last (max) date:

for instance:

Date,   EmployeeGroup,   Employee

01.01.2019, 4, Alex

08.07.2019, 6, Alex

The EmployeeGroup is a Level of Career development, so on 01.01.2019, Alex = 4 (Business Developer), and on 08.07.2019 Alex is = 6 (Projektmanager), therefore i should take into account the row:

Date,  EmployeeGroup, Employee, Sales 

08.07.2019, 6, Alex, 12

i' heard that can be solved by using of function: peek().

i hope i explained well

Thanks a lot

[JordyWegman]

Hi Beck,

That is clear now, then we can try the following. First we will get the max date for the employee and we make a combination of this as a key. This key we will use as a filter on your Employee data. The result is only the information with the max date per employee.

tmpTable:
Load
Max(Date) & '|' & Employee as %DateEmployee,
Employee
Group by Employee
;
Load * Inline [
Date, EmployeeGroup, Employee, Sales
01.01.2019, 4, Alex, 5
01.01.2018, 6, Alex, 6
01.01.2017, 8, Alex, 7
];

FinalTable:
Load
Date,
EmployeeGroup,
Employee,
Sales
From [YourSource]
Where Exist (%DateEmployee, Date & '|' & Employee)
;

Drop table tmpTable;

Jordy

Climber