topic Re: Select the active record in QlikView

Select the active record

xarapre7 — Wed, 25 Nov 2020 16:16:04 GMT

Hello!

I have data that shows employee_key, employee_name, employee_id, emp_status and country.
An employee can have multiple emp_status. My expression in the text box is below:

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/
Count({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} Aggr(If(Sum({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

Right now, it's picking whatever is the 1st record for that employee which doesn't tie up to any country so the output it gives is 0.

EMP_KEY, EMP_NAME, EMP_ID, EMP_STATUS, COUNTRY
123             JOE               12001      WITHDRAWN     -
123             JOE               12001      WITHDRAWN     -
234             JOE               12010      ACTIVE              US

What's the right expression to only display country from the active record?

Thanks in advance for your help!

Re: Select the active record

Gysbert_Wassenaar — Fri, 30 Dec 2016 09:19:35 GMT

What's the right expression to only display country from the active record?

Only({<EMP_STATUS={'ACTIVE'}>}COUNTRY)

Re: Select the active record

Anil_Babu_Samineni — Fri, 30 Dec 2016 09:20:29 GMT

You written this from Text box, Do you mean text object?

Text object won't get the string by using Set analysis. Can you describe more with expected output

Try Something like this, I mean merge this to show only Active Data point

EMP_STATUS={'ACTIVE'}

Re: Select the active record

xarapre7 — Fri, 30 Dec 2016 14:25:08 GMT

Thanks for the reply. I tried something like below and it's still giving me 0.

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/

Count({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} Aggr(If(Sum({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} Only({<EMP_STATUS={'ACTIVE'}>} COUNTRY), CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

Did I put it in the right location?

Re: Select the active record

Miguel_Angel_Baeyens — Fri, 30 Dec 2016 14:33:30 GMT

Set analysis works anywhere in QlikView where an expression is accepted. Text objects do work with set analysis, if the expression is correct.

Re: Select the active record

Miguel_Angel_Baeyens — Fri, 30 Dec 2016 14:35:42 GMT

Have not tested, but should be something like:

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/Count({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY), EMP_STATUS={'ACTIVE'}>} Aggr(If(Sum({$<EMPLOYEE_NAME=,COUNTRY=p(COUNTRY)>} CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

Re: Select the active record

sunny_talwar — Fri, 30 Dec 2016 14:35:53 GMT

Not entirely sure what you goal is, but may be this:

=Num(Sum({1} Aggr(Sum({$<EMPLOYEE_NAME,COUNTRY=p(COUNTRY)>} CALLS)/

Count({$<EMPLOYEE_NAME =,COUNTRY = p(COUNTRY)>} Aggr(If(Sum({$<EMPLOYEE_NAME =,COUNTRY = p(COUNTRY), EMP_STATUS={'ACTIVE'}>} CALLS) > 0, 1), DATE, EMPLOYEE_NAME=)), DATE)),'#,##0')

Re: Select the active record

sunny_talwar — Fri, 30 Dec 2016 14:37:08 GMT

mbaeyens‌

This doesn't seem right

Re: Select the active record

Miguel_Angel_Baeyens — Fri, 30 Dec 2016 14:41:23 GMT

Yep, as I said, I have not tested it, just copypasted the expression from the post above. Anyway, I have fixed it now (still untested)

Re: Select the active record

Anil_Babu_Samineni — Fri, 30 Dec 2016 15:05:25 GMT

Agree 100%, By using set operator how do we get String. Can we have any hierarchy to get the string or atleast agenda.

Re: Select the active record

Miguel_Angel_Baeyens — Fri, 30 Dec 2016 15:13:09 GMT

Using the Only() function will return a value, either it be a literal string or a number. If you need to further format it, you can use functions like Text() or Date#(), or etc. If Only() returns more than one value, then you need to use a Max() or Rank(), or FirstSortedValue() or Aggr() function to make sure you are returning one single value. But again, regardless the type of value, you can represent text in a text object using those functions.

Some examples:

Find out min and max Dates and show a value only from this row

What's the aggr dimension value for the highest value of expression

Count items with max date

*** Equivalent to VLookup in Excel (Returning a String Value) by Using Expression *** (Simple Question)

and eventually Displaying the Top Salesperson in a Textbox

Re: Select the active record

xarapre7 — Fri, 30 Dec 2016 20:15:37 GMT

Still gives me 0.

Re: Select the active record

sunny_talwar — Fri, 30 Dec 2016 20:18:27 GMT

Without the red part you are getting what you want? Just by adding EMP_STATUS={'ACTIVE'} the output becomes 0?

Re: Select the active record

xarapre7 — Fri, 30 Dec 2016 20:24:14 GMT

Is there a way to do the filter in the DataModel? If so, can you please let me know?

Thank you all for your kind assistance.

Re: Select the active record

sunny_talwar — Fri, 30 Dec 2016 20:27:36 GMT

You are only looking to load data where EMP_STATUS = ACTIVE? If that's true, then load with where statement

LOAD .....

FROM ....

Where EMP_STATUS = 'ACTIVE';

Re: Select the active record

xarapre7 — Fri, 30 Dec 2016 20:29:14 GMT

I tired it with and without the red part, the result is 0

Re: Select the active record

sunny_talwar — Fri, 30 Dec 2016 20:30:45 GMT

So your original expression isn't working then?

Re: Select the active record

xarapre7 — Fri, 30 Dec 2016 20:31:43 GMT

I only need to pull the Country data for record that is "ACTIVE" if there are multiple records for that employee.

The above expression will filter for just the "ACTIVE" for all the records of employees.

Re: Select the active record

sunny_talwar — Fri, 30 Dec 2016 20:32:52 GMT

Yes, it will pull all those rows where EMP_STATUS = ACTIVE

Re: Select the active record

xarapre7 — Fri, 30 Dec 2016 20:49:48 GMT

Actually, I need to pull all the records of the employees may it be withdrawn or active at the DataModel level. But in the UI, that's where I'm having the challenge of filtering the record based on the employee's active record given the above expression. The expression is pulling the withdrawn record. Even with the set analysis of EMP_STATUS={'ACTIVE'}, it's still not giving the desired result.