get the last record MesAno

[EmmaMark]

// Carregar os dados
LOAD * INLINE [
Mesano, Codigocontabancaria, Data, Codigo, Balance
"dez 2023", 107, "31/12/2023", 351289, 1638.05
"dez 2023", 107, "31/12/2023", 351288, -8361.95
"dez 2023", 108, "20/12/2023", 349958, 1638.05
"dez 2023", 108, "20/12/2023", 349957, -3641.95
"dez 2023", 108, "14/12/2023", 348936, 1638.05
"dez 2023", 106, "14/12/2023", 348935, 601.86
"dez 2023", 106, "13/12/2023", 348793, 1638.05

];

get the last one using

ordem by, Mesano, Codigocontabancaria, Data, Codigo

desired result

LOAD * INLINE [
Mesano, Codigocontabancaria, Data, Codigo, Balance
"dez 2023", 107, "31/12/2023", 351289, 1638.05
"dez 2023", 108, "20/12/2023", 349958, 1638.05
"dez 2023", 106, "14/12/2023", 348935, 601.86

];

[LRuCelver · Accepted solution]

I'm assuming you want the latest entry for every Codigocontabancaria and that within a day the latest entry has the largest value in Codigo:

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
Inline [
Mesano, Codigocontabancaria, Data, Codigo, Balance
"dez 2023", 107, "31/12/2023", 351289, 1638.05
"dez 2023", 107, "31/12/2023", 351288, -8361.95
"dez 2023", 108, "20/12/2023", 349958, 1638.05
"dez 2023", 108, "20/12/2023", 349957, -3641.95
"dez 2023", 108, "14/12/2023", 348936, 1638.05
"dez 2023", 106, "14/12/2023", 348935, 601.86
"dez 2023", 106, "13/12/2023", 348793, 1638.05
]
Group By Mesano, Codigocontabancaria, Data;

[theoat · Accepted solution]

Hello ! 

Try this :
NoConcatenate
Test2:
LOAD
FirstValue(Mesano),
Codigocontabancaria,
FirstValue(Data),
FirstValue(Codigo),
FirstValue(Balance)
Resident Test
Group By Codigocontabancaria;

Enjoy your Qlik.

Kind regards,
Théo ATRAGIE.

[BrunPierre · Accepted solution]

Simply add these lines to the previously loaded table

Inner Join ([Carregar os dados])
LOAD Max(Codigo) as Codigo
Resident [Carregar os dados]
Group By Codigocontabancaria;

[EmmaMark]

TRANKS

[EmmaMark]

Dados:
LOAD
CODIGOCONTABANCARIA AS Codigocontabancaria,
DATA AS Data,
MonthName(DATA) as Mesano,
CODIGO AS Codigo,

Balance

FROM
[C:\Users\vasco\Desktop\ProcessedData.qvd]
(qvd);

How do I correct this inline table for qvd.🤔

?????????

Inline [
Mesano, Codigocontabancaria, Data, Codigo, Balance
"dez 2023", 107, "31/12/2023", 351289, 1638.05
"dez 2023", 107, "31/12/2023", 351288, -8361.95
"dez 2023", 108, "20/12/2023", 349958, 1638.05
"dez 2023", 108, "20/12/2023", 349957, -3641.95
"dez 2023", 108, "14/12/2023", 348936, 1638.05
"dez 2023", 106, "14/12/2023", 348935, 601.86
"dez 2023", 106, "13/12/2023", 348793, 1638.05
]
Group By Mesano, Codigocontabancaria, Data;

[EmmaMark]

correct the position

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
Inline [
Mesano, Codigocontabancaria, Data, Codigo, Balance
"dez 2023", 107, "31/12/2023", 351289, 1638.05
"dez 2023", 107, "31/12/2023", 351288, -8361.95
"dez 2023", 108, "20/12/2023", 349958, 1638.05
"dez 2023", 108, "20/12/2023", 349957, -3641.95
"dez 2023", 108, "14/12/2023", 348936, 1638.05
"dez 2023", 106, "14/12/2023", 348935, 601.86
"dez 2023", 106, "13/12/2023", 348793, 1638.05
]
Group By Mesano, Codigocontabancaria, Data; ok 
?????????????????
Dados:
LOAD 
CODIGOCONTABANCARIA AS Codigocontabancaria, 
DATA AS Data, 
MonthName(DATA) as Mesano,
CODIGO AS Codigo, 

Balance

FROM
[C:\Users\vasco\Desktop\ProcessedData.qvd]
(qvd);

[EmmaMark]

adptar. Dados:
LOAD
CODIGOCONTABANCARIA AS Codigocontabancaria,
DATA AS Data,
MonthName(DATA) as Mesano,
CODIGO AS Codigo,

Balance

FROM
[C:\Users\vasco\Desktop\ProcessedData.qvd]
(qvd);

[EmmaMark]

desired result, but changing from inline to qvd table, I need to adapt the script is correct. I'm just not able to adapt.

Dados:
LOAD 
CODIGOCONTABANCARIA AS Codigocontabancaria, 
DATA AS Data, 
MonthName(DATA) as Mesano,
CODIGO AS Codigo, 

Balance

FROM
[C:\Users\vasco\Desktop\ProcessedData.qvd]
(qvd);

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;

[EmmaMark]

desired result, but changing from inline to qvd table, I need to adapt the script is correct. I'm just not able to adapt.

Dados:
LOAD 
CODIGOCONTABANCARIA AS Codigocontabancaria, 
DATA AS Data, 
MonthName(DATA) as Mesano,
CODIGO AS Codigo, 

Balance

FROM
[C:\Users\vasco\Desktop\ProcessedData.qvd]
(qvd);

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;