topic Finding max in QlikView

Finding max

Thu, 03 Apr 2014 21:40:21 GMT

Can someone please help with this.

This is the source data in the table

Header 1	Header 2	Header 3	Header 4	Header 5
Alex	1/1/2013	T1	a1	10
Alex	1/1/2013	T1	a1	11
Alex	1/1/2013	T1	a2	30
Alex	1/1/2013	T1	a2	40
Alex	1/1/2013	T2	a2	10
Alex	1/1/2013	T2	a2	13
Alex	1/1/2013	T2	a2	15

Header4 has two values a1 and a2.

a1 takes priority.whenever a1 availabe need to ignore a2 records.

objective is to find max(header5).

so the final result would be

Header 1	Header 2	Header 3	Header 4	Header 5
Alex	1/1/2013	T1	a1	11
Alex	1/1/2013	T2	a2	15

Thanks....

Re: Finding max

maxgro — Thu, 03 Apr 2014 22:29:11 GMT

RESULT

SCRIPT

DIRECTORY;

LOAD [Header 1],

[Header 2],

[Header 3],

[Header 4],

[Header 5]

FROM

[http://community.qlik.com/thread/113110]

(html, codepage is 1252, embedded labels, table is @1);

load * where key<>peek(key);

NoConcatenate

load

rowno() as id,

[Header 1] & '-' & [Header 3] as key,

Resident s

order by [Header 1], [Header 3], [Header 4], [Header 5] desc;

DROP Table s;

Re: Finding max

Fri, 04 Apr 2014 00:33:56 GMT

Anyway ..... i hope you like it .

Have fun with QV:)

regards

Darek

Re: Finding max

arjunkrishnan — Fri, 04 Apr 2014 06:15:48 GMT

Hai poojananda,

I here by Attach qvw File ..

Pls Find My Attach It's Useful For U...

Regards

ArjunKrish

Re: Finding max

Fri, 04 Apr 2014 06:33:24 GMT

Arjun,

as my license finished please show Header 5 expression. I'am interested if you have found another way than I to resolve it.

regards

Darek

Re: Finding max

arjunkrishnan — Fri, 04 Apr 2014 06:41:12 GMT

Hai Darek

If([Header 3]='T1' and [Header 4]='a1',aggr(Max([Header 5]),[Header 4]),

If([Header 3]='T2',aggr(Max([Header 5]),[Header 3])))

Re: Finding max

Fri, 04 Apr 2014 06:47:33 GMT

Hi DARIUSZ MIELCZAREK

in ur Demo U Just Add These steps

LOAD [Header 1],

[Header 2],

[Header 3],

[Header 4],

[Header 5]

FROM

max.xls

(biff, embedded labels, table is [Sheet1$]);

LOAD * Where key<> Peek(Key);

load *,

[Header 1] & '-' & [Header 3] as key,

Resident A

order by [Header 1], [Header 3], [Header 4], [Header 5] desc;

DROP Table A;

in table chart give header 1,2,3,5 as dimensions and Expression is ur =Aggr(MinString([Header 4]),[Header 1],[Header 2], [Header 3]) expression..

try this ..................

Re: Finding max

Fri, 04 Apr 2014 06:52:58 GMT

Hey check this..!!

Re: Finding max

Fri, 04 Apr 2014 07:05:52 GMT

Amay,

your expression finds max, but over Heder3.

As i understand, Pojananda wants to:

1. Aggregate Header 4 over Heder1-3.

2. find max Heder 5 for Header4 callculated in step 1.

My solution calculates exactly this. Even if we will have more tahn 2 values to consider in Heder 4. So it is dynamic approach. I;am also afraid that my method may has poor performance. This is why i'am interested if somebody has some another idea.

@Arjun - i'am afraid, that your expression will not work if we will load some different data into Heder3 or Header4

reagards

Darek

Re: Finding max

sunilkumarqv — Fri, 04 Apr 2014 07:31:09 GMT

@your main objective is to find max Header 5 you probably written wrong out put

so the final result would be like these not 11 .

Header 1	Header 2	Header 3	Header 4	Header 5
Alex	1/1/2013	T1	a1	40
Alex	1/1/2013	T2	a2	15

for best results find attached

Re: Finding max

Fri, 04 Apr 2014 08:01:40 GMT

As Poojananda told:

Header4 has two values a1 and a2. a1 takes priority.whenever a1 availabe

need to ignore a2 records.

The only question is If there arr only 2 priority values. If YES, there may

be If or pick be used. But If priority has more possible values, we need

more dynamic approach.

04-04-2014 09:31 użytkownik "Sunil Kumar" <qcwebmaster@qlik.com>

napisał:

Qlik Community <http://community.qlik.com/> Finding max
reply from Sunil Kumar<http://community.qlik.com/people/sunilkumarqv?et=watches.email.thread>in *New
to QlikView* - View the full discussion<http://community.qlik.com/message/502205?et=watches.email.thread#502205>

Re: Finding max

Fri, 04 Apr 2014 09:15:35 GMT

I just found very nice solution!

1. Lets assume for a moment, that we load additional integer column (i named it "priority"). It has values corresponding to header 4, but without letter a. So, it has values 1 for a1, 2 for a2, etc.

2. Lets assume, that we are able during reload create expression body like this (and for exaple put it into variable value).:

pick(min(priority), max({<priority={1}>}[Header 5]),max({<priority={2}>}[Header 5]),max({<priority={3}>}[Header 5]),....max({<priority={n}>}[Header 5]) )

(I have no time to prepare load script which will build this expression, but it is not difficult)

in my case expression is:

pick(min(priority), max({<priority={1}>}[Header 5]),max({<priority={2}>}[Header 5]),max({<priority={3}>}[Header 5])),

because in my tests max priority in all popullation has value 3.

3. If we have above done, solution is easy and, should has normal performance.

Take a look on last column in chart.

Have fun with QV

regards

Darek

Re: Finding max

Fri, 04 Apr 2014 16:59:53 GMT

Thank you so much.It seems working.Can you please explain me the expression code.

Re: Finding max

Fri, 04 Apr 2014 17:00:41 GMT

Hey all thank you so much for all these ideas.You guys rock!

Re: Finding max

Fri, 04 Apr 2014 17:02:19 GMT

Priority has only two values,a1 and a2.no third value for that column.

Re: Finding max

Fri, 04 Apr 2014 17:04:37 GMT

Hi Sunil-a1 has only two values 10 and 11 in the above example.a1 takes priority over a2.so need to ignore the record where header5 is 40.

Re: Finding max

Fri, 04 Apr 2014 17:07:50 GMT

I donot think I can go with this option.Because header3 has multiple values,cannot hardcod with
If([Header 3]='T1' or If([Header 3]='T2'.Only header4 has two static values a1 and a2.Sorry may be I was not clearly mentioned this in the requirements.

Re: Finding max

Fri, 04 Apr 2014 20:04:42 GMT

Please take expression from comm_header1_5_v3.qvw I attached in one of my responses.

1. column with Header4 is Calculated Dimension:

=aggr(minstring([Header 4]),[Header 1],[Header 2],[Header 3])

So, i simple gives you minimal valye of H4 for combination of H1,H2,H3

2. Header 5 column:

pick(min(priority), max({<priority={1}>}[Header 5]),max({<priority={2}>}[Header 5]),max({<priority={3}>}[Header 5]))

pick(n,ex1,ex2,ex3,...,exm)

n-integer value, in our case n=min(priority)

pick() gives you n'th expression. So, if our priority = 2, there will be ex2

our expression are very similiar to each other:

max({<priority={1}>}[Header 5]) is taken when priority =1

max({<priority={2}>}[Header 5]) is taken when priority =2,

.......

Belive me, even if today you have only 2 priorities, there may be someday 3-rd priority So, be ready for it

If you need some more information, please let me know.

regards

Darek

Re: Finding max

Mon, 07 Apr 2014 16:58:14 GMT

Darek..Thank you so much for your time to explain the code.Have a great day.