Max Subquery

[#1]

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?

[Anonymous]

Now it's time to explain in words what you want to achieve...

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

returns every single value of run_id because you group it by itself.  What did I get wrong?

[#3]

so I've done the SQL query.

Can you translate it to qlikview, because I'm a beginner in QlikView?

Thanks,

Miguel

[#4]

Here's my latest Script and still not working:

qualify *;

Raw:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\Raw.tabdel]

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

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([Aggregates.RUN_ID]) as MAX_RUN_ID

resident Aggregates

group by [Aggregates.RUN_ID];

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

MaxAggregates:

LOAD RUN_ID,

     EXECUTION_DATE

FROM

[..\Data\MIB1-FOO-aggregates.tabdel]

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

where RUN_ID = $(mypeek);

unqualify *;

[Anonymous]

Let me ask you the last time before I give up

What is the difference in results between:

1) your SQL Query

select *

from Aggregates A

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

I have to add that your query doesn't work for me.  This works:

select *

from Aggregates A

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

2) and this SQL

select *

from Aggregates A

The "where" condition as you have it now doesn't do anything.

3) and your code (first part):

Aggregates:

LOAD

     RUN_ID,

     EXECUTION_DATE

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

[ronaldocarrijo]

So I'm carrying twice to show it to u the original data and the data with Max, the more u will only use the second load

[#7 · Accepted solution]

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);

[#8]

So maybe you should quit, if you don't understand that:

RUN_ID VALUE

1               a

1               b

2               c

2               d

So my final comment marked as an answer just retrieves the rows where RUN_ID = 2, and if there a ROW_ID called 12, and 12 is the biggest RUN_ID, my script only return rows with 12 as RUN_ID.

[Anonymous]

"So maybe you should quit..." - thank you for this suggestion, I'll certainly consider it

Now, look again at my very first reply.  To make it easier for you, I'm repeating it here:

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;

Look carefully - it works the same way as your solution, only a little faster.  It even looks similar...   Logically you're doing exactly the same, except you're using variable and additional "last run", while I'm using "inner keep".

I try to learn from everyone.  You'll be fine if you do it too.

[Anonymous]

I can make it even a little shorter by using inner join:

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

inner join (Aggregates)
load max(RUN_ID)  as RUN_ID
resident Aggregates;

[#11]

The problem is that your initial solution didn't work. Also your solution isn't faster because I need both tables.

Since my problem is solved now I'll not test your new proposed solutions.

Of course I like to learn with others but you're being a little arrogant here.