topic Creating a table that always selects the highest value in QlikView

Creating a table that always selects the highest value

Wed, 25 Nov 2020 16:16:04 GMT

Hello, I am new to Qliview and i have the simplified table below.

Depreciation_History

OBJECT_ID	YEAR	PERIOD	AMOUNT	UNIQUE_NO.
00001	2017	01	20	2017001
00001	2017	02	20	2017002
00001	2017	03	20	2017003
00002	2017	01	50	2017001
00002	2017	02	50	2017002
00002	2017	03	50	2017003
00003	2017	01	100	2017001
00003	2017	02	100	2017002
00003	2017	03	100	2017003

I am trying to get the table below where the highest UNIQUE_NO. is always selected

OBJECT_ID	YEAR	PERIOD	AMOUNT	UNIQUE_NO.
00001	2017	03	20	2017003
00002	2017	03	50	2017003
00003	2017	03	100	2017003

I have tried the code below but it doesn't work

Dep:
LOAD

OBJECT_ID,
AMOUNT,
FA_YEAR,
FA_PERIOD,
UNIQUE_NO

Resident Depreciation_History

WHERE UNIQUE_NO like Max(UNIQUE_NO);

I would welcome any help and thank you for your time

Re: Creating a table that always selects the highest value

its_anandrjs — Thu, 24 Aug 2017 15:58:56 GMT

Did you want to kep ony Max values or another table you required.

Re: Creating a table that always selects the highest value

Thu, 24 Aug 2017 16:08:47 GMT

i would like another table please that returns the information like below.

OBJECT_ID	YEAR	PERIOD	AMOUNT	UNIQUE_NO.
00001	2017	03	20	2017003
00002	2017	03	50	2017003
00003	2017	03	100	20170 03

Re: Creating a table that always selects the highest value

its_anandrjs — Thu, 24 Aug 2017 16:09:43 GMT

Then try this ways

Source:

LOAD * Inline

[

OBJECT_ID, YEAR, PERIOD, AMOUNT, UNIQUE_NO.

00001, 2017, 01, 20, 2017001

00001, 2017, 02, 20, 2017002

00001, 2017, 03, 20, 2017003

00002, 2017, 01, 50, 2017001

00002, 2017, 02, 50, 2017002

00002, 2017, 03, 50, 2017003

00003, 2017, 01, 100, 2017001

00003, 2017, 02, 100, 2017002

00003, 2017, 03, 100, 2017003

];

NoConcatenate

Temp:

LOAD

OBJECT_ID,

Max(UNIQUE_NO.) as MaxUnique

Resident Source

Group By OBJECT_ID;

NoConcatenate

FInal:

LOAD * Resident Source Where Exists(MaxUnique,UNIQUE_NO.);

DROP Table Source;

DROP Table Temp;

See attached also

Re: Creating a table that always selects the highest value

Thu, 24 Aug 2017 16:14:59 GMT

thank you, i will give that a go. just about to leave for a holiday. I will try when i return. kind regards and thank you for your time

Re: Creating a table that always selects the highest value

Mon, 11 Sep 2017 13:11:01 GMT

Awesome, that works a treat , thank you very much

Re: Creating a table that always selects the highest value

vishsaggi — Mon, 11 Sep 2017 13:55:54 GMT

Hello Vernon,

Can you close the thread Marking correct/helpful responses accordingly.

Thanks,
V.

Re: Creating a table that always selects the highest value

effinty2112 — Mon, 11 Sep 2017 15:02:53 GMT

Hi Vernon,

Add this to your script after loading Depreciation_History:

Inner Join

LOAD

OBJECT_ID,

max(UNIQUE_NO.) as UNIQUE_NO.

Resident Depreciation_History Group by OBJECT_ID;

Returns:

OBJECT_ID	YEAR	PERIOD	AMOUNT	UNIQUE_NO.
00001	2017	03	20	2017003
00002	2017	03	50	2017003
00003	2017	03	100	2017003

Cheers

Andrew