subquery in qlik sense expression

[anilmahanty]

Hi,

I have a scenario as below,

need to select employee promotion details with respecto to period selection from Qlik sense filters (Year/Qtr/Month)

In the Sql It will looks like:

select Ename, Grade, PromotionDate from Emp_Fact
where (Eid, PromotionDate) in (Select Eid, max(PromotionDate) from Promotion_fact
                                                                  where PromotionDate = (Qlik sense filter)
                                                                   group by Eid)

So in Qlik sense, how to achieve this, and this need to show in 2 place as total count of promotion done for the period and list of employee name with Grade

Please help me on this scenario and let me know incase need any furhter details

[JordyWegman]

Hi Anilmahanty,

Maybe something like this?

Load
  Ename,
  Grade,
  Max(PromotionDate) as PromotionDate
Group by Ename, Grade
;
Load
  Ename,
  Grade,
  PromotionDate
from Emp_Fact
where Eid = PromotionDate

Maybe you can show something about the result that you want to see. That would make it easier to get a solution.

Jordy

Climber

[Gysbert_Wassenaar]

I think you're looking for the FirstSortedValue function.

Use Ename as dimension and two measures: max(PromotionDate) and FirstSortedValue(Grade, -PromotionDate)

[anilmahanty]

I need this in output in UI side,

1. in table with List of employee name

2. In Text Box: Total count of emp

[anilmahanty]

I need to to show the count of employee for the period with few more condition.

[JordyWegman]

I'll rephrase my question, but I'm not interested in the visualizations, but what are the conditions that need to be met for the extraction? When are the right rows selected?

Jordy

Climber

[anilmahanty]

Hi Jordy,

Its like, i am having complete history of data in DB table, lets take 10 yr data of emp promotion.(2010-2019) and other table are there as well. where i can not do any aggression in the script.

Requirement: need to represent, count of employee and their names, who are eligible for promotion.

Eligible promotion criteria : who are not promoted from past 3 yr.

By default its show eligible promotion for 2019, i.e. when no date filter selected. i.e. prediction for future

If i make any date selection, like: Year/Month/Quarter

then the count/list should show, who all were eligible for promotion during that selected period, as per the Eligible promotion criteria. i.e. past data analysis

Promotion for employee, will available consecutive years as well, i.e. employee would have got promotion back to back 2 year or alternate year, any combinations.

So i have to take the latest promotion date for the each employee and compare with the date selection from filter. 

Please let me know, in case need more detail or having any confusion.