Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
beck_bakytbek
Master
Master

Set expression: Group of Max Date

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

Labels (1)
7 Replies
JordyWegman
Partner - Master
Partner - Master

Hi,

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

Jordy

Climber

Work smarter, not harder
beck_bakytbek
Master
Master
Author

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
Partner - Master
Partner - Master

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 

Work smarter, not harder
beck_bakytbek
Master
Master
Author

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
Partner - Master
Partner - Master

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

Work smarter, not harder
beck_bakytbek
Master
Master
Author

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
Partner - Master
Partner - Master

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

Work smarter, not harder