BRING THE LAST MONTH OF THE YEAR, ON YOUR ACCOUNT

[EmmaMark]

BRING ONLY THE LATEST REGISTRATION PER MONTH AND YEAR, ACCORDING TO THE ATTACHED QVD AND EXAMPLE BELOW. I DON'T WANT THE OTHER RECORDS

DADOS:
LOAD Codigocontabancaria,
DATA,
Mesano,
Codigo,
Balance
FROM
[C:\Users\vasco\Desktop\DADO.qvd]
(qvd);

CORRECT RESULT

EXAMPLE OTHER ACCOUNTS..

Mesano	Codigocontabancaria	DATA	Codigo	Balance
dez 2023	107	31/12/2023	351289	1638,0499999999
dez 2023	108	26/12/2023	350723	35099,39
dez 2023	113	29/12/2023	351202	7283,74

OTHER ACCOUNTS....

//

I NEED TO ADPT THE SCRIPT ,

IT'S WRONG

Data:
Load
FirstSortedValue(Mesano, -Data) as Mesano,
Codigocontabancaria,
Date(Max(Data)) as Data,
FirstSortedValue(Codigo, -Data) as Codigo,
FirstSortedValue(Balance, -Data) as Balance
Group By Codigocontabancaria;
Load
Mesano, 
Codigocontabancaria, 
Data, 
Max(Codigo) as Codigo,
FirstSortedValue(Balance, -Codigo) as Balance

[rubenmarin · Accepted solution]

Hi this could be dodne using exists(), so once a combination of codigocuentabancaria and Mesano has been loaded, the other similar records aren't loaded.

OriginalData:
LOAD Codigocontabancaria, 
     DATA, 
     Mesano, 
     Codigo, 
     Balance
FROM [DataSource];

OnlyLastValue:
LOAD
  *,
  Codigocontabancaria &'_'& Mesano as loaded
Resident OriginalData
Where not exists('loaded',Codigocontabancaria &'_'& Mesano)
Order By Codigocontabancaria, Codigo desc;

DROP Table OriginalData;

If instead of Measno you want to use the month-year fram the data field you can use:

OnlyLastValue:
LOAD
  *,
  Codigocontabancaria &'_'& MonthName(DATA) as loaded
Resident OriginalData
Where not exists('loaded',Codigocontabancaria &'_'& MonthName(DATA))
Order By Codigocontabancaria, DATA desc;

[EmmaMark]

Fantastic

😁