Creating a table that always selects the highest value

[#1]

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

[its_anandrjs]

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

[#3]

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

[its_anandrjs]

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

[#5]

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

[#6]

Awesome, that works a treat , thank you very much

[vishsaggi]

Hello Vernon,

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

Thanks,
V.

[effinty2112]

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