Finding max

[#1]

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....

[maxgro]

RESULT

SCRIPT

DIRECTORY;

s:

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);

t:

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;

[#3 · Accepted solution]

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

Have fun with QV:)

regards

Darek

[arjunkrishnan]

Hai poojananda,

I here by Attach qvw File ..

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

Regards

ArjunKrish

[#5]

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

[arjunkrishnan]

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])))

[#7]

Hi  DARIUSZ MIELCZAREK

in ur Demo U Just Add These steps

A:

LOAD [Header 1],

     [Header 2],

     [Header 3],

     [Header 4],

     [Header 5]

FROM

max.xls

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

B:

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 ..................

[#8]

Hey check this..!!

[#9]

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

[sunilkumarqv]

@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