topic Re: Using Aggregate into a Button field in QlikView

Using Aggregate into a Button field

Wed, 31 Aug 2016 08:58:58 GMT

Hi All,

I'm trying to use a calculated expression into a button, I am now putting this:

=aggr(count(distinct [Don - Key]),[Pers - Code])

into the field and

as search string.

It's not working, so I'm not sure. Can I use an expression into the Field? What am I doing wrong?

Thank you for your help,

Diego

Re: Using Aggregate into a Button field

sunny_talwar — Wed, 31 Aug 2016 09:37:30 GMT

For select in field action? I doubt. What exactly are you trying to do? Select Pers - Code where Count(DISTINCT [Don - Key]) = 2?

Re: Using Aggregate into a Button field

Anonymous — Wed, 31 Aug 2016 09:37:50 GMT

Hi Diego,

On field, you have to return the name of a field, and on expression the value. What do you wanna do with this button?

Which action are you using?

Regards!

Re: Using Aggregate into a Button field

Wed, 31 Aug 2016 09:45:27 GMT

I'll try to explain

I have a database with people ([Pers - Code] is the code) and they are doing actions that I store in a different table ([Don - Key] is the key of this actions).

I'd like to create a button that give me the possibility to choose people that do a number of actions (2 is just an example) in the current set of selections (so I can't do it in the script).

Using " aggr(count(distinct [Don - Key]),[Pers - Code]) " I can have the number of actions for each people, but I can't create a button to do selections on this

Re: Using Aggregate into a Button field

Wed, 31 Aug 2016 09:45:35 GMT

I'll try to explain

I have a database with people ([Pers - Code] is the code) and they are doing actions that I store in a different table ([Don - Key] is the key of this actions).

I'd like to create a button that give me the possibility to choose people that do a number of actions (2 is just an example) in the current set of selections (so I can't do it in the script).

Using " aggr(count(distinct [Don - Key]),[Pers - Code]) " I can have the number of actions for each people, but I can't create a button to do selections on this

Re: Using Aggregate into a Button field

Anonymous — Wed, 31 Aug 2016 11:39:52 GMT

Hi Diego,

Create a variable:

vVar = Concat(Distinct [Don - Key],',')

Now, edit your button action:

On field put [Pers - Code].

On expression put: '('&Concat({<[Don - Key]={$(vVar)}>} Distinct [Pers - Code],'|')&')'

Regards!

Re: Using Aggregate into a Button field

Wed, 31 Aug 2016 12:08:11 GMT

Sorry for the dumb question, I'm trying but it seems not working to me.. Where do I say that if I push the button I select the value 2, 3 or a different one?

Kind regards

Diego

Re: Using Aggregate into a Button field

Anonymous — Thu, 01 Sep 2016 06:08:08 GMT

Hi Diego, with my code you select the people who has the keys selected. If you want to do it with static numbers, you don't need the variable.

'('&Concat({<[Don - Key]={2}>} Distinct [Pers - Code],'|')&')'

Regards!!

Re: Using Aggregate into a Button field

Thu, 01 Sep 2016 06:55:18 GMT

Thank you for your help, I'm still not able to make it works but I'm trying to understand why, probably I just need to study better the Set analysis statement.

From what I can read in your, you expect to find a [Don - Key] equals to 2, is it correct? If yes, it can't works for me because I need to count the keys for each person and select the people who have a number of action equals to 2

Re: Using Aggregate into a Button field

Anonymous — Thu, 01 Sep 2016 08:47:20 GMT

Please, attach one example of your app, plz.

Re: Using Aggregate into a Button field

Thu, 01 Sep 2016 09:14:26 GMT

Thank you so much for your patience Manuel,

in attach an example of what I'm doing (this one has informations from an excel to do it faster, in the real one I am connected to a database).

Inside you can find:

- a table of people

- a table of actions, linked to people

- a list of aggregated actions

- 4 buttons (it's just an example)

What I am missing right now is to have the buttons working on this field I am calculating (if I press 2 in aggregate, I have people with 2 actions, 3 people with 3 actions etc).

Also, I cannot modify and put the aggregation in the script because I need to have it changing depending on the selecions I am doing.

Thanks again

Diego

Re: Using Aggregate into a Button field

Anonymous — Thu, 01 Sep 2016 12:52:15 GMT

Hi Diego,

With your example is more clear that you want to do.

1.- On each button create one action to Select Field

2.- On Field write your field [Pers - Code]

3.- On respect button write this:

For 1 action: ='('&Concat({<[Don - Key]={"=aggr(count(distinct [Don - Key]), [Pers - Code])=1"}>} Distinct [Pers - Code],'|')&')'
For 2 actions:='('&Concat({<[Don - Key]={"=aggr(count(distinct [Don - Key]), [Pers - Code])=2"}>} Distinct [Pers - Code],'|')&')'
For 3 action2:='('&Concat({<[Don - Key]={"=aggr(count(distinct [Don - Key]), [Pers - Code])=3"}>} Distinct [Pers - Code],'|')&')'
For 4 actions:='('&Concat({<[Don - Key]={"=aggr(count(distinct [Don - Key]), [Pers - Code])=4"}>} Distinct [Pers - Code],'|')&')'

This expression returns an string with the values of Pers-Code concatenated, and separated by | (qlikview standard for select more than one value) on Don-Key (calculated by =aggr(count(distinct [Don - Key]), [Pers - Code])) are 1, 2, 3 or 4.

Regards!!

Re: Using Aggregate into a Button field

Thu, 01 Sep 2016 13:09:14 GMT

Thank you!!!!!

Re: Using Aggregate into a Button field

Anonymous — Thu, 01 Sep 2016 15:27:00 GMT

You're wellcome