topic subquery in qlik sense expression in App Development

subquery in qlik sense expression

anilmahanty — Sat, 16 Nov 2024 20:30:30 GMT

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

Re: subquery in qlik sense expression

JordyWegman — Mon, 08 Jul 2019 14:56:19 GMT

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

Re: subquery in qlik sense expression

Gysbert_Wassenaar — Mon, 08 Jul 2019 15:14:01 GMT

I think you're looking for the FirstSortedValue function.

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

Re: subquery in qlik sense expression

anilmahanty — Mon, 08 Jul 2019 18:16:34 GMT

I need this in output in UI side,

1. in table with List of employee name

2. In Text Box: Total count of emp

Re: subquery in qlik sense expression

anilmahanty — Mon, 08 Jul 2019 18:19:52 GMT

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

Re: subquery in qlik sense expression

JordyWegman — Tue, 09 Jul 2019 06:33:30 GMT

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

Re: subquery in qlik sense expression

anilmahanty — Wed, 10 Jul 2019 15:04:23 GMT

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.