Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Try MaxString() instead of Max.
Try MaxString() instead of Max.
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')