Thanks for your response. That explain why I was getting invalid expression. The table I am trying to use Max Function, it has more than 25 fields. I guess it won't be ideal to use all these fields under Group By clause.
I already used your solution 2, which creates a synthetic as I am using more than 1 fields as Group By. Not a big fan of synthetic key.
My example should not lead to synthetic key, as it only has one common key field.
If you has multiple key field, you could either join the result back to the base table or create a composite key in the base table and use that as the common key field.
Aggregation during load requires that you define how the aggregation is expected to be grouped. Meaning that you need to define over which dimensions you want the table to be aggregated over, which is done through GROUP BY.
Fields defined in the load statement must be aggregations or be included i GROUP BY. In your example the fields grade and date are not following this rule, and I expect that is why the reload fails.
The structure of your script really depends on what you are trying to accomplish. This could be one way to add an aggregated max value to your data model, in two separate tables associated over the ID field.
MyTable: LOAD * FROM ……..\SourceData.xlsx] (ooxml, embedded labels, table is Sheet1)
LOAD ID, max(date) as NewDate Resident MyTable Group By ID;
Thanks for your response. That explain why I was getting invalid expression. The table I am trying to use Max Function, it has more than 25 fields. I guess it won't be ideal to use all these fields under Group By clause.
I already used your solution 2, which creates a synthetic as I am using more than 1 fields as Group By. Not a big fan of synthetic key.
My example should not lead to synthetic key, as it only has one common key field.
If you has multiple key field, you could either join the result back to the base table or create a composite key in the base table and use that as the common key field.
