Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max Subquery

Aggregates:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LastRunId:

load max(run_id) resident Aggregates;

AggregatesForLastRun:

load * Resident Aggregates

where run_id >= LastRunId;

Any way to make this work?

1 Solution

Accepted Solutions
Not applicable
Author

Problem solved!

Aggregates:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MaxRunId:

load max([RUN_ID]) as MAX_RUN_ID

resident Aggregates;

let mypeek = peek('MAX_RUN_ID',0,'MaxRunId');

LastRunAggregates:

LOAD RUN_ID,

     EXECUTION_DATE

resident Aggregates

where RUN_ID = $(mypeek);

View solution in original post

19 Replies
ronaldocarrijo
Partner - Contributor III
Partner - Contributor III

Thus have the same effect, more simply:

Aggregates:

LOAD Max(RUN_ID)           as RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

GROUP BY EXECUTION_DATE;

Anonymous
Not applicable
Author

I assume there can't be run_id > LastRunId, can be only run_id = LastRunId
Try this:

Aggregates:
LOAD
RUN_ID,
EXECUTION_DATE
FROM [..\Data\foo.tabdel] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);


LastRunId:
inner keep (Aggregates)
load max(RUN_ID)  as RUN_ID
resident Aggregates;

DROP TABLE LastRunId

Not applicable
Author

Your solution doesn't work....

Not applicable
Author

I want a solution like Michael that uses the table already in memory instead of reloading again from the source and wasting time.

Not applicable
Author

My last code can't find column [AggregatesForLastRun.MAX_RUN_ID]...

Raw:

LOAD RUN_ID,

     EXECUTION_DATE,

   

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

qualify *;

Aggregates:

LOAD RUN_ID,

     EXECUTION_DATE

    

FROM

[..\Data\foo.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

AggregatesForLastRun:

load max([Aggregates.RUN_ID]) as MAX_RUN_ID Resident Aggregates

Group BY [Aggregates.RUN_ID];

LastRunId:

load * Resident Aggregates

where [Aggregates.RUN_ID] >= [AggregatesForLastRun.MAX_RUN_ID];

unqualify *;

Not applicable
Author

In SQL this is just so easy...Why do they have to complicate?

select *

from Aggregates A

where A.RUN_ID = select max(run_id) from Aggregates A2 group by A2.RUN_ID

ronaldocarrijo
Partner - Contributor III
Partner - Contributor III

Works yes my friend, attached an example I did.

Not applicable
Author

This also doesn't work:

Aggregates:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\FOO.tabdel]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MaxRunId:

inner keep (Aggregates)

load max(RUN_ID)  as MAX_RUN_ID

resident Aggregates;

LastRunId:

load * Resident Aggregates

where [RUN_ID] = MAX_RUN_ID;

Not applicable
Author

But you're loading the same table twice.

If you have 1 million rows that;s not feasible in terms of performance.