Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Did you want to kep ony Max values or another table you required.
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 |
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
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
Awesome, that works a treat , thank you very much
Hello Vernon,
Can you close the thread Marking correct/helpful responses accordingly.
Thanks,
V.
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