Flag Max Value

[didierodayo]

Hello,

I have this Load script and am trying to flag the max and min within the load script.

Anyone with an idea of what the best method will be to achieve the required result

load * inline [

Type, Month, Amount

A,1,6

A,1,8

B,2,4

B,2,3

C,3,7

C,3,5

D,4,9

D,4,3

E,5,10

E,5,4

F,6,9

F,6,2

G,7,5

G,7,6

];

Expected result

Type	Month	Amount	Rank
A	1	6	0
A	1	8	1
B	2	3	0
B	2	4	1
C	3	5	0
C	3	7	1
D	4	3	0
D	4	9	1
E	5	4	0
E	5	10	1
F	6	2	0
F	6	9	1
G	7	5	0
G	7	6	1

Thanks

[Sergey_Shuklin · Accepted solution]

Hello, Didier!

temp:

load * inline [

Type, Month, Amount

A,1,6

A,1,8

B,2,4

B,2,3

C,3,7

C,3,5

D,4,9

D,4,3

E,5,10

E,5,4

F,6,9

F,6,2

G,7,5

G,7,6

];

left join (temp)

load Type,Month, max(Amount) as Amount, 1 as Rank resident temp group by Type,Month;

//this can be omitted

NoConcatenate

result:

Load Type, Month, Amount, if(isnull(Rank),0,1) as Rank resident temp;

DROP TABLE temp;

[Kushal_Chawda]

Data:

load * inline [

Type, Month, Amount

A,1,6

A,1,8

B,2,4

B,2,3

C,3,7

C,3,5

D,4,9

D,4,3

E,5,10

E,5,4

F,6,9

F,6,2

G,7,5

G,7,6

];

New:

LOAD *,

     if(Type<>Previous(Type),0,1) as Rank

Resident Data

order by Type, Month, Amount;

DROP Table Data;

[balabhaskarqlik]

WebData:

LOAD Type,

     Month,

     Amount

FROM

[https://community.qlik.com/thread/265033]

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

New:

LOAD *,

    If((Type <> Previous(Type)),0,1) as Rank

Resident WebData Order by Type;

DROP Table WebData;