topic Re: SET Analysis With Aggr(),Count(),Max() in QlikView

SET Analysis With Aggr(),Count(),Max()

Anonymous — Mon, 11 Dec 2017 05:59:20 GMT

Hi All,

Kindly help me out in the below scenario,

I am Having the ID Column in Straight table and I need to use the Expression with Set analysis With all Three Function( Aggr(),Count(),Max() ) and what will be resultant output.

I have tried the below Script but not Working.i know my script is wrong,

=Aggr(Count(iD),(max({<ID>}id)))

ID Set Expression()

do the needful!

stalwar1‌marcowedel‌marcus_sommer‌devarasu07‌jonvitale‌pradosh_thakur‌

Re: SET Analysis With Aggr(),Count(),Max()

Anil_Babu_Samineni — Mon, 11 Dec 2017 06:08:11 GMT

May be this?

Max(Aggr(Count(ID), id))

Re: SET Analysis With Aggr(),Count(),Max()

Anonymous — Mon, 11 Dec 2017 06:16:10 GMT

hi anil,

it need to be in Set analysis

Re: SET Analysis With Aggr(),Count(),Max()

Anil_Babu_Samineni — Mon, 11 Dec 2017 06:17:42 GMT

This is also called set analysis..

Re: SET Analysis With Aggr(),Count(),Max()

Anonymous — Mon, 11 Dec 2017 06:33:31 GMT

hi the Scenario is need to have the Aggr() function in first

Re: SET Analysis With Aggr(),Count(),Max()

Anil_Babu_Samineni — Mon, 11 Dec 2017 06:35:39 GMT

Not sure your business , May be helps When should the Aggr() function NOT be used?

Re: SET Analysis With Aggr(),Count(),Max()

Anonymous — Mon, 11 Dec 2017 06:38:06 GMT

Hi,

it is not up to the Requirement this Scenario was faced in the Interview so i post to get the clarification how we can achieve it

Thanks,

Re: SET Analysis With Aggr(),Count(),Max()

Anil_Babu_Samineni — Mon, 11 Dec 2017 06:46:16 GMT

You may ask Interviewer exactly what you are trying to get. Can you make it as example as we have rights if we are not understand there manner.

So, If you want something please free to make example and result set needed..

Re: SET Analysis With Aggr(),Count(),Max()

devarasu07 — Mon, 11 Dec 2017 06:48:04 GMT

Hi,

Would be able to share your expected output in excel format? may be u can try like below,

Method 1:

=FirstSortedValue(<Dimension>, -Aggr(Count(ID), <Dimension>)) //FirstSortedValue with "-" will return the max value of the dimension

Method 2:

count({<ID={"=Aggr(max(ID),ID)"}>}ID)

Method 3:

=max( aggr( count(ID),<Dimension> ))

example, <Dimension> is ID then u can write like below

Max( aggr ( Count(ID) , ID) )

also u can check this article,

Set Analysis in the Aggr function

https://www.analyticsvidhya.com/blog/2014/02/aggr/

Regards,

Deva

Re: SET Analysis With Aggr(),Count(),Max()

Anonymous — Mon, 11 Dec 2017 06:58:57 GMT

Hi,

it is not up to the Requirement this Scenario was faced in the Interview so i post to get the clarification how we can achieve it

they asked me u have ID Column

And Values are 10,20,30,40

in set analysis write the Expression within aggr() function with count(id)and Max(id)

and what will be output

So i didn't get that so only i have posted to get the clarification on this scenario

Thanks,

Re: SET Analysis With Aggr(),Count(),Max()

marcus_sommer — Mon, 11 Dec 2017 08:18:19 GMT

Most interview questions are only stupid and completely meaningless if there is no declared aim which result should be calculated. Just writing an expression with certain functions is like writing any sentences with 10 (certain or respectively random) words ... the results won't be useful in any way ...

Beside this with a bit of guessing it might be something like this:

count(aggr(max({< Dim3 = {'AnyValue'}>} id), Dim1, Dim2))

but like above mentioned each question needs a sensible context otherwise it's meaningless.

- Marcus

Re: SET Analysis With Aggr(),Count(),Max()

jonvitale — Mon, 11 Dec 2017 14:04:03 GMT

Can you try to explain what you are trying to achieve here? It seems strange to me that you are neither specifying the goal of the expression nor the expected output. How can we possibly help you if we do not know this?

Do you want an expression with ANY possible application of the Aggr, Count, and Max function that also includes some form of set analysis? If so, something like:

Aggr(Count({<FieldWeWantMaxOf = {$(Max(FieldWeWantMaxOf))}>} FieldToCount) , DimToAggregateOn)

This is a shot in the dark, I'd like to hear your specifications more though.

Re: SET Analysis With Aggr(),Count(),Max()

Anonymous — Tue, 12 Dec 2017 02:34:54 GMT

Hi Jonathan,

It is the interview question and i just got Confused like u what is need to achieve,

that's y i posted here to get the clarification on this question

Re: SET Analysis With Aggr(),Count(),Max()

pradosh_thakur — Tue, 12 Dec 2017 02:46:59 GMT

As the interviewer didn't specify the aim of the question to you we can only guess what the answer would be. There can be many answer but i think the reason why he asked the question is "Nested aggregation can't be used together". For example count(max(sales)). so that may be the reason he/she asked you how will you write it using the aggr function and this is just a guess.

Hopefully you cleared the interview

Pradosh