topic Re: Max function in Load Script without creating resident table in QlikView

Max function in Load Script without creating resident table

saifuddin — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

Just wondering can Max function be used in load script without creating a Resident Table.

LOAD

ID,
grade,
date,
max(date) as NewDate

FROM ……..\SourceData.xlsx] (ooxml, embedded labels, table is Sheet1)

Group By ID;

This comes up as Invalid Expression. With resident table it works file.

Thanks

Saif

Re: Max function in Load Script without creating resident table

ToniKautto — Mon, 25 Feb 2019 03:01:55 GMT

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;

Re: Max function in Load Script without creating resident table

shiveshsingh — Mon, 18 Feb 2019 05:59:47 GMT

You forgot to add other fields in Group by clause. That's why it is throwing error.

Re: Max function in Load Script without creating resident table

saifuddin — Mon, 18 Feb 2019 23:07:17 GMT

Hi Tko,

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.

Thanks

Re: Max function in Load Script without creating resident table

ToniKautto — Mon, 25 Feb 2019 03:01:21 GMT

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.

Re: Max function in Load Script without creating resident table

saifuddin — Tue, 26 Feb 2019 03:33:32 GMT

Thanks I am able to avoid synthetic by using join.