Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
anilmahanty
New Contributor III

subquery in qlik sense expression

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

Labels (2)
6 Replies
Partner
Partner

Re: subquery in qlik sense expression

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

Work smarter, not harder
MVP & Luminary
MVP & Luminary

Re: subquery in qlik sense expression

I think you're looking for the FirstSortedValue function.

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


talk is cheap, supply exceeds demand
anilmahanty
New Contributor III

Re: subquery in qlik sense expression

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
New Contributor III

Re: subquery in qlik sense expression

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

Partner
Partner

Re: subquery in qlik sense expression

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

Work smarter, not harder
anilmahanty
New Contributor III

Re: subquery in qlik sense expression

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.