Select max Data

[ikenna94]

Hello,

I have a set of excel data that I want to load in my qlik sense app, with cell values as eg. x12345-01, x12345-01, x12345-03. How do I load the data to select only the max value which in this case is "x12345-03"?

I tried the below but did not work.

LOAD    
    max column1

    column2

    column3

FROM [lib://xxxxxxxxxxxx.xlsm]
(ooxml,xxxxxxxxxxxxxxx);

[ToniKautto]

Did not validate my syntax, but something in line with this...

You need to first identify the largest value. Your example has text value, so you could use MaxString() to find the largest text value. Then only load the records from source that match the found value. In Qlik you can use Exists() as a value t compare with previously loaded values. Fianlly dropt he temp max value table.

TmpMaxValue:
LOAD MaxString(column1) AS MaxValue
FROM [lib://xxxxxxxxxxxx.xlsm]
(ooxml,xxxxxxxxxxxxxxx);

LOAD
column1
column2
column3
FROM [lib://xxxxxxxxxxxx.xlsm]
(ooxml,xxxxxxxxxxxxxxx)
WHERE Exists(MaxValue, column1);

DROP Table TmpMaxValue;

[ikenna94]

Not working! It gives me an error message:

The following error occurred:

Field 'MaxValue' not found

The error occurred here:

?

[ToniKautto · Accepted solution]

Change the order of the values in Exists() to WHERE Exists(MaxValue, column1);