Basic Load Not Working

[quickqlik2]

The table I have looks like this, with two text columns:

Product ID	Year
PMX99	Calendar Year 2022
PMX99	Calendar Year 2021
ORA31	Calendar Year 2022
ORA31	Calendar Year 2021

For each Product ID, I want to return the MAX(Year). The output would look like this (expected)

Product ID	Year
PMX99	Calendar Year 2022
ORA31	Calendar Year 2022

The code I have is

Table1:
LOAD
    "Product ID",
    MAX("Year") as "Year"
FROM [lib://excel.xlsx](ooxml, embedded labels, table is [Sheet1])
GROUP BY "Product ID";

Which produces (actual)

Product ID	Year
PMX99	-
ORA31	-

I checked my source data and all Year values are "Calendar Year 2020", "Calendar Year 2021", etc. with no `-`, no nulls, no blanks. Why are my Year values all `-`? What is the correct code?

[Digvijay_Singh · Accepted solution]

Try MaxString() instead of Max.

[daturpin]

Your column has "Calendar Year 2022", which is a string, not a numeric value. Before this load will work, you need to do some more massaging to make this a numeric value or the MAX() command won't work.

The simplest method is KeepChar('Year', '1234567890')