topic Re: Flag Max Value in QlikView

Flag Max Value

didierodayo — Thu, 22 Jun 2017 05:25:06 GMT

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

Re: Flag Max Value

Sergey_Shuklin — Thu, 22 Jun 2017 05:35:22 GMT

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;

Re: Flag Max Value

Kushal_Chawda — Thu, 22 Jun 2017 06:49:22 GMT

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;

Re: Flag Max Value

BalaBhaskar_Qlik — Thu, 22 Jun 2017 09:48:15 GMT

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;