Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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);
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;
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
Your solution doesn't work....
I want a solution like Michael that uses the table already in memory instead of reloading again from the source and wasting time.
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 *;
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
Works yes my friend, attached an example I did.
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;
But you're loading the same table twice.
If you have 1 million rows that;s not feasible in terms of performance.