topic Re: To Find Max value in a One Column in QlikView

To Find Max value in a One Column

pra_kale — Sat, 09 Jan 2016 06:46:21 GMT

Hi,

Below given is the sample data in which for one Policy there are Two or Three Premium values. I want a maximum value for that policy only through script so that will become my base data for further calculation.

Please suggest how I can achieve this in script

Policy_no	Premium	Name
12345678	5000	Jon
10002345	100000	Joseph
12345688	40000	Kern
12345278	60000	Ritu
12345678	3000	Jon
10002345	200000	Joseph
12345688	60000	Kern
12345278	40000	Ritu
12345688	100000	Kern
12345278	800000	Ritu
5678945	25000	Jeevan
369874	123980	Kiran

Final Out_put should be like this..

Policy_no	Premium	Name
12345678	5000	Jon
10002345	200000	Joseph
12345688	100000	Kern
12345278	800000	Ritu
5678945	25000	Jeevan
369874	123980	Kiran

Thanks in advance..

Re: To Find Max value in a One Column

Sat, 09 Jan 2016 07:02:09 GMT

select max(column_name)

Re: To Find Max value in a One Column

petter — Sat, 09 Jan 2016 07:11:16 GMT

Here is how you can achieve it in a load script:

LOAD

Policy_no,

Max(Premium),

FirstSortedValue(Name,Premium)

RESIDENT

GROUP BY

Policy_no;

Re: To Find Max value in a One Column

petter — Sat, 09 Jan 2016 07:13:20 GMT

and if you don't need the A table at all for other purposes you could make this into a preceding load like this:

LOAD

Policy_no,

Max(Premium),

FirstSortedValue(Name,Premium)

GROUP BY

Policy_no;

LOAD Policy_no,

Premium,

Name

FROM

[sample_Max value.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: To Find Max value in a One Column

petter — Sat, 09 Jan 2016 07:15:24 GMT

or directly from the Excel file without preceding load:

LOAD

Policy_no,

Max(Premium),

FirstSortedValue(Name,Premium)

FROM

[sample_Max value.xlsx]

(ooxml, embedded labels, table is Sheet1)

GROUP BY

Policy_no;

Re: To Find Max value in a One Column

pra_kale — Sat, 09 Jan 2016 07:28:25 GMT

That's Great...Both the solutions are Helpful..

But, currently for further use I will go with Second solution..

Just One question..If I have more than 3 or 4 columns then I have to include in a script like this. Please correct me if I am wrong.. or any other way to this.

FirstSortedValue(Name,Premium,Column 3, Column 4, Column 5, Column 6,...etc).

Re: To Find Max value in a One Column

Kushal_Chawda — Sat, 09 Jan 2016 07:33:10 GMT

In this case you can do like this

FirstSortedValue(Column 3,Premium) as Column 3,

FirstSortedValue(Column 4,Premium) as Column 4

FirstSortedValue(Column 5,Premium) as Column 5

and so on

Re: To Find Max value in a One Column

petter — Sat, 09 Jan 2016 07:38:40 GMT

Adding a third parameter will only pick the n'th value so a number 2 for the third parameter will not select the first sorted value but the second sorted value. So you have to go for just using two parameters like: FirstSortedValue(Column N, Premium) AS ColN for each additional column.

Re: To Find Max value in a One Column

pra_kale — Sat, 09 Jan 2016 08:18:05 GMT

Thanks for Your Help...That's Great.

Re: To Find Max value in a One Column

pra_kale — Sat, 09 Jan 2016 08:22:31 GMT

Too Good..Great..Thanks for Your Help.

Re: To Find Max value in a One Column

petter — Sat, 09 Jan 2016 08:39:49 GMT

Happy to help Please mark the question as answered so the thread can be closed.

Re: To Find Max value in a One Column

timsaddler — Fri, 18 Mar 2016 14:46:08 GMT

Very good thread Petter - rescued me from a difficult situation

Thank you