show highest value in script

[cedfoning]

Hello, i have an issue i need help please. 

I have the following data : 

date	ID	Amount	Code	Transaction ID
Total		742,11
15/02/2024	224	259,67	2	2024021508312521
15/02/2024	224	185,35	2	2024021514530321

I want a script that gives me the amount with the highest transaction id. 

in this exemble, the result should be one lie with the amount 185,35

[cedfoning]

Hello, i have an issue i need help please. 

I have the following data : 

date	ID	Amount	Code	Transaction ID
Total		742,11
15/02/2024	224	259,67	2	2024021508312521
15/02/2024	224	185,35	2	2024021514530321

I want a script that gives me the amount with the highest transaction id. 

in this exemble, the result should be one lie with the amount 185,35

[PhanThanhSon]

Hi,

try this:

Community:

LOAD
    "date",
    ID,
    "Amount",
    Code,
    TransactionID
FROM [lib://Community/Mappe1.xlsx]
(ooxml, embedded labels, table is Tabelle1);

MINMAX_TABLE:
MAPPING Load num(max([Transaction ID])) AS KEY,
        1 AS Flag; 
Load FieldValue('TransactionID',IterNo()) as [Transaction ID]
autogenerate(1)  
while not IsNull(FieldValue('TransactionID',Iterno()));


NoConcatenate
FinalTable:
Load *,
 APPLYMAP('MINMAX_TABLE', TransactionID, 0) AS FlagMax
Resident Community;

DROP TABLE Community;

Best regards Son

[cedfoning]

Hello, 

thanks for your help

here is what i executed 

and here is the result 

I don't have the same anwser as you. 

Could you please help ? thanks

[PhanThanhSon · Accepted solution]

Hi,

can you check this step:

Community:
LOAD
    "date",
    ID,
    "Amount",
    Code,
    TransactionID
FROM [lib://Community/Mappe1.xlsx]
(ooxml, embedded labels, table is Tabelle1);

MINMAX_TABLE:
Load 
 num(max([Transaction ID])) AS KEY,
        1 AS Flag; 
Load FieldValue('TransactionID',IterNo()) as [Transaction ID]
autogenerate(1)  
while not IsNull(FieldValue('TransactionID',Iterno()));

IF your KEY Field is empty here your Transaction ID is not formatted as a number. Maybe you should check if your field is formatted as a number. You can check this like this:

Left alignment means text, right alignment means number formatted.

Or check it in the data model:

In Addition:

You can evaluate your great numbers with the evaluate function:

LOAD *,
Evaluate(TransactionID2) AS Test 
Inline [
TransactionID2 
2024021508312520
2024021514530320
];

Best regards Son

[cedfoning]

Hello, it's ok, thanks. 

following this screenshot, 

We have 2 amounts for code = 2 (in this case we take the amount whose transaction ID is the largest) and the other amount for code = 1.

Which will be 297.09+185.35 

Is that possible ? 

Thanks for your help

[PhanThanhSon]

Hello,

in this case you need to group your MinMaxTable;

Community:
LOAD
    "date",
    ID,
    "Amount",
    Code,
    TransactionID
FROM [lib://Community/Mappe1.xlsx]
(ooxml, embedded labels, table is Tabelle1);

MINMAX_TABLE:
MAPPING Load Key, 
 1 AS FLAG
Load Code 
 max([Transaction ID]) AS KEY 
RESIDENT Community
Group by Code;

NoConcatenate
FinalTable:
Load *,
 APPLYMAP('MINMAX_TABLE', TransactionID, 0) AS FlagMax
Resident Community;

DROP TABLE Community;

I havent tried this code in Qlik yet. But i think this could solve your problem.

Best regards Son