First unique distinct values sorted by date

[Sebastian_Dec]

Hi I have a table with 50000 unique records for ArtId, I am trying to get one unique ArtId along with COD-TAU for the latest Year_Month.

Something like this:

ArtId	DOS-COD	COT-COD	Month	Year	Year_Month
10069	1000	2	7	2021	2021.07
10069	1000	4	7	2022	2022.07
10069	1000	42	5	2023	2023.05
10069	1000	40	7	2023	2023.07

The result for this record should be:

10069

1000

40

7

2023

2023.07

I try use FirstValue() and FirstSortedValue() function but I always end up with 37 records (as many as there are unique records for "COT-COD",  instead of e.g. 50000 unique records for ArtId.

Dossie1000_tmp:
Load
    FirstValue(ArtId) as ArtId,
    //FirstSortedValue(ArtId, Year_Month),
    //"DOS-COD",
    "COT-COD",
    Year_Month
    //Month, 
    //Year
Resident DataFakturyTable
Group By Year_Month, "COT-COD"

What I do wrong?

[Sebastian_Dec · Accepted solution]

First of all, I would like to thank @BrunPierre , @Lech_Miszkiewicz , @Or very much for Your help, especially You @marcus_sommer .

After some digging in the code, I managed to divide the unique values for DOS-COD 1000 and those other than 1000, while for DOS-COD 1000 the value with the largest date (as a number) is taken into account.

I paste my code below and mark  as the second solution if someone wants to have a copy/paste format. It can be improved, for example, to take ArtId from DOC-COD <> 1000 by NumDate, but I don't have the strength or need for that... 

//the Faktury table is a bit of a jumble and gluing together many tables, there is no special tab code apart from: Num([HVE-DATFAC]) as NumDate,
Faktury_Calosc:
Load
DostawaSklep,
NoFaktury,
ArtId, //Article ID
WartoscNettoNaFakturze,
MiesiącFaktury, //MonthInvoices
RokFaktury, //YearInvoice
NumDate, 
NazwaDostawcy,
DostawcaId,
Pogr_kod1,
FakturaKorygująca,
"MBF-COD", 
"MBF-LIB"
Resident Faktury;

STORE Faktury_Calosc INTO [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Faktury_Calosc.qvd]
(qvd);

Dossie1000_tmp_tmp: 
load 
    ArtId, 
    "DOS-COD", 
    "COT-COD", 
    NumDate 
Resident Faktury;

Drop Table Faktury;

inner join(Dossie1000_tmp_tmp) 
load 
	ArtId, 
    max(NumDate) as NumDate 
    resident Dossie1000_tmp_tmp 
group by ArtId;


Dossie1000_tmp:
Load
    FirstValue(ArtId) as ArtId,
    //FirstSortedValue(ArtId, NumDate),
    "DOS-COD",
    "COT-COD",
    NumDate
Resident Dossie1000_tmp_tmp
Group By NumDate, "COT-COD", "DOS-COD"
;

NoConcatenate
Dossie1000:
Load
    ArtId,
    "DOS-COD",
    "COT-COD"
Resident Dossie1000_tmp_tmp
Where "DOS-COD" = 1000 ;
;

STORE Dossie1000_tmp_tmp INTO [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Dossie1000_tmp_tmp.qvd]
(qvd);
STORE Dossie1000 INTO [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Dossie1000.qvd]
(qvd);

Drop Table Dossie1000_tmp_tmp;
//you need to remove Faktury_Calosc because Where not Exists(ArtId); refers to the entire code, not selected tables
Drop Table Faktury_Calosc;

NoConcatenate
ArtId_NotIn1000:
Load
    Distinct ArtId,
    "DOS-COD",
    "COT-COD"
//NumDate
//Resident Faktury
FROM [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Dossie1000_tmp_tmp.qvd]
(qvd)
Where not Exists(ArtId);


//we load back the tables with our data.
Faktury_Calosc:
LOAD *
FROM [lib://QlikContainers\Prod\QlikRaporty\2.QVD\Faktury_Calosc.qvd]
(qvd);